﻿using Coldairarrow.Business.PB;
using Coldairarrow.Entity.Base;
using Coldairarrow.Entity.DTO;
using Coldairarrow.Entity.ListSelection;
using Coldairarrow.Entity.PB;
using Coldairarrow.IBusiness;
using Coldairarrow.Util;
using Coldairarrow.Util.ClassLibrary;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Options;
using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace Coldairarrow.Api.Controllers.PB
{
    [Route("/PB/[controller]/[action]")]
    public class PB_DesignListController : BaseApiController
    {
        #region DI
        readonly IOperator _operator;
        private readonly RedisOptions _redisOptions; //1.声明实体
        private readonly MongoDBOptions _mongodbOptions;
        public PB_DesignListController(IPB_DesignListBusiness pB_DesignListBus, IOperator @operator, IPB_MaterialBusiness pB_MaterialBus, IPB_DesignDetailBusiness pB_DesignDetailBus, IOptions<MongoDBOptions> mongodbOptions, IOptions<RedisOptions> redisOptions)
        {
            _pB_MaterialBus = pB_MaterialBus;

            _pB_DesignListBus = pB_DesignListBus;
            _operator = @operator;
            _pB_DesignDetailBus = pB_DesignDetailBus;
            _mongodbOptions = mongodbOptions.Value;
            _redisOptions = redisOptions.Value;//3.赋值
        }
        IPB_DesignDetailBusiness _pB_DesignDetailBus { get; }

        IPB_MaterialBusiness _pB_MaterialBus { get; }

        IPB_DesignListBusiness _pB_DesignListBus { get; }

        #endregion

        #region 获取

        [HttpPost]
        public async Task<PageResult<PB_DesignListBulidingDTO>> GetDataList(PageInput<ConditionDTO> input)
        {
            return await _pB_DesignListBus.GetDataListAsync(input);
        }
        /// <summary>
        /// 朱柏清 2022-03-03 发货通知单中选择楼号
        /// </summary>
        /// <param name="projectId"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<List<BuildingnumberDTO>> GetBuildingnumberDataList(string projectId)
        {
            return await _pB_DesignListBus.GetBuildingnumberDataListAsync(projectId);
        }
        [HttpPost]
        public async Task<PageResult<PB_DesignListBulidingDTO>> GetDataListItem(PageInput<ConditionDTO> input)
        {
            return await _pB_DesignListBus.GetDataListItemAsync(input);
        }

        /// <summary>
        /// 清单审核列表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<PageResult<PB_DesignList>> GetDataListToExamine(PageInput<ConditionDTO> input)
        {
            return await _pB_DesignListBus.GetDataListToExamineAsync(input);
        }

        /// <summary>
        /// 清单审核
        /// </summary>
        /// <param name="id"></param>
        /// <param name="stateType"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task ToExamineDataAsync(string id, int stateType)
        {
            await _pB_DesignListBus.ToExamineDataAsync(id, stateType);
        }

        [HttpPost]
        public async Task<PB_DesignList> GetTheData(IdInputDTO input)
        {
            return await _pB_DesignListBus.GetTheDataAsync(input.id);
        }
        /// <summary>
        /// 通过清单编号
        /// </summary>
        /// <param name="ProjectId">设计清单</param>
        /// <returns></returns>
        [HttpPost]
        public async Task<List<DataTypeInfo>> GetDataLists(string ProjectId)
        {
            var res = await _pB_DesignListBus.GetDataLisJson(ProjectId);
            return res;
        }

        /// <summary>
        /// 任务单有源选择物料
        /// </summary>
        /// <param name="ProjectId">设计清单Id</param>
        /// <returns></returns>
        [HttpPost]
        public async Task<PageResult<PB_MaterialSelectDTO>> GetDataSelectListAsync(PageInput<ConditionDTO> input, string ParentId)
        {
            var res = await _pB_DesignListBus.GetDataSelectListAsync(input, ParentId);
            return res;
        }

        /// <summary>
        /// 任务单选择清单
        /// </summary>
        /// <param name="ProjectId">设计清单Id</param>
        /// <returns></returns>
        [HttpPost]
        public async Task<PageResult<PB_DesignList>> GetDataSelectAsync(PageInput<DesignListConditionDTO> input)
        {
            var res = await _pB_DesignListBus.GetDataSelectAsync(input);
            return res;
        }

        /// <summary>
        /// 获取设计清单明细是成品的物料
        /// </summary>
        /// <param name="designId">设计清单Id</param>
        /// <returns></returns>
        [HttpPost]
        public async Task<List<PB_DesignListDataDTO>> GetDesignDetailPrintList(string designId)
        {
            return await _pB_DesignListBus.GetDesignDetailPrintListAsync(designId);
        }

        /// <summary>
        /// 设计清单成品物料打印
        /// </summary>
        /// <param name="input">设计清单id+打印集合</param>
        /// <returns></returns>
        [HttpPost]
        public async Task PrintDesignDetailList(MaterialNumDTO input)
        {
            await _pB_DesignListBus.PrintDesignDetailListAsync(input, _mongodbOptions, _redisOptions);
        }

        /// <summary>
        /// 设计清单成品物料明细
        /// </summary>
        /// <param name="designdetailId">设计清单明细Id</param>
        /// <returns></returns>
        [HttpPost]
        public async Task<List<PB_JobBookingQrCodeDTO>> GetDesignDetailJobBookingQrCode(string designdetailId)
        {
            return await _pB_DesignListBus.GetDesignDetailJobBookingQrCodeAsync(designdetailId);
        }

        /// <summary>
        /// 设计清单成品物料补打
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task PrintDesignDetailJobBookingQrCode(PB_DesignDetailPrintDataDTO data)
        {
            await _pB_DesignListBus.PrintDesignDetailJobBookingQrCodeAsync(data, _mongodbOptions);
        }

        /*
       * 开发人：朱庆龙
       * 提交日期：2022-02-22 15:08
       * 修改内容：新增设计清单明细排序 Slot
       * 上传SVN：是
       */
        [HttpPost]
        public async Task<DesignListDataDTO> GetDesignListData(string id, int deatiltype)
        {
            return await _pB_DesignListBus.GetDesignListDataAsync(id, deatiltype);
        }

        #endregion

        #region 提交
        /// <summary>
        /// 添加设计清单/修改
        /// </summary>
        /// <param name="data">设计清单</param>
        /// <returns></returns>
        [HttpPost]
        public async Task SaveData(PB_DesignList data)
        {
            if (data.Id.IsNullOrEmpty())
            {
                InitEntity(data);

                await _pB_DesignListBus.AddDataAsync(data);
            }
            else
            {
                await _pB_DesignListBus.UpdateDataAsync(data);
            }
        }
        /// <summary>
        /// 修改设计清单
        /// </summary>
        /// <param name="data">修改</param>
        /// <returns></returns>
        //[HttpPost]
        //public async Task UpdateDateList(PB_DesignDTO data) {
        //    if (data.Id.IsNullOrEmpty())
        //    {
        //        await _pB_DesignListBus.UpdateDataList(data);
        //    }
        //}
        /// <summary>
        /// 删除设计清单
        /// </summary>
        /// <param name="ids">删除</param>
        /// <returns></returns>
        [HttpPost]
        public async Task<AjaxResult> DeleteData(List<string> ids)
        {
            return await _pB_DesignListBus.DeleteDataAsync(ids);
        }

        /// <summary>
        /// 新建保存设计清单及清单明细
        /// </summary>
        /// <param name="data">保存设计清单及明细</param>
        /// <returns></returns>
        [HttpPost]
        public async Task SaveListData(PB_DesignDTO data)
        {
            if (data.Id.IsNullOrEmpty())
            {
                InitEntity(data);

                await _pB_DesignListBus.AddData(data);
            }
            else
            {
                await _pB_DesignListBus.UpdateData(data);
            }
        }

        /// <summary>
        /// 编辑设计清单下的物料详情添加
        /// </summary>
        /// <param name="data">保存设计清单及明细</param>
        /// <returns></returns>
        [HttpPost]
        public async Task SaveMaterialData(SaveMaterialData data)
        {
            if (data.PId.IsNullOrEmpty())
            {
                InitEntity(data);

                await _pB_DesignListBus.AddMaterialData(data);
            }
            else
            {
                await _pB_DesignListBus.UpdateMaterialData(data);
            }


        }

        /// <summary>
        /// 清单变更
        /// </summary>
        /// <param name="id">清单ID</param>
        /// <returns></returns>
        [HttpPost]
        public async Task UpdateDesignListDataChange(string id)
        {
            await _pB_DesignListBus.UpdateDesignListDataChangeAsync(id);
        }

        /// <summary>
        /// 清单提交审核
        /// </summary>
        /// <param name="Id">清单Id</param>
        /// <returns></returns>
        [HttpPost]
        public async Task UpdateDataState(string Id)
        {
            await _pB_DesignListBus.UpdateDataStateAsync(Id);
        }


        /// <summary>
        /// 设计模板下载 
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        [NoCheckJWT]
        public IActionResult ExportToExcel()
        {
            //创建EXCEL工作薄
            string importExcelPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"wwwroot/excelModel/设计清单导入模板.xlsx");
            IWorkbook workBook = WorkbookFactory.Create(importExcelPath);
            //工作流写入，通过流的方式进行创建生成文件
            using (MemoryStream stream = new MemoryStream())
            {
                workBook.Write(stream);
                byte[] buffer = stream.ToArray();

                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", string.Format("清单信息表_{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")));
            }

        }

        /// <summary>
        /// 料台模板下载 
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        [NoCheckJWT]
        public IActionResult ExportMaterialTableToExcel()
        {
            //创建EXCEL工作薄
            string importExcelPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"wwwroot/excelModel/料台清单导入模板.xlsx");
            IWorkbook workBook = WorkbookFactory.Create(importExcelPath);
            //工作流写入，通过流的方式进行创建生成文件
            using (MemoryStream stream = new MemoryStream())
            {
                workBook.Write(stream);
                byte[] buffer = stream.ToArray();

                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", string.Format("料台清单信息表_{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")));
            }

        }

        /// <summary>
        /// 补加模板下载 
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        [NoCheckJWT]
        public IActionResult ExportSupplement()
        {
            //创建EXCEL工作薄
            string importExcelPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"wwwroot/excelModel/补加单模板.xlsx");
            IWorkbook workBook = WorkbookFactory.Create(importExcelPath);
            //工作流写入，通过流的方式进行创建生成文件
            using (MemoryStream stream = new MemoryStream())
            {
                workBook.Write(stream);
                byte[] buffer = stream.ToArray();

                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", string.Format("补加单_{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")));
            }

        }

        /// <summary>
        /// 料台变更模板
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [NoCheckJWT]
        public IActionResult MaterialTableChangeTemplate() {
            string importExcelPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"wwwroot/excelModel/料台变更单模板.xlsx");
            IWorkbook workBook = WorkbookFactory.Create(importExcelPath);
            using (MemoryStream stream = new MemoryStream()) {
                workBook.Write(stream);
                byte[] buffer = stream.ToArray();
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", string.Format("料台变更单_{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")));
            }
        }
        /// <summary>
        /// 变更模板下载 
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        [NoCheckJWT]
        public IActionResult ExportOhange()
        {
            //创建EXCEL工作薄
            string importExcelPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"wwwroot/excelModel/变更单模板.xlsx");
            IWorkbook workBook = WorkbookFactory.Create(importExcelPath);
            //工作流写入，通过流的方式进行创建生成文件
            using (MemoryStream stream = new MemoryStream())
            {
                workBook.Write(stream);
                byte[] buffer = stream.ToArray();

                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", string.Format("变更单_{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")));
            }

        }
        /// <summary>
        /// 变更单导入
        /// </summary>
        /// <param name="file"></param>
        /// <param name="ProjectId"></param>
        /// <param name="BuildingNumberID"></param>
        /// <returns></returns>
        public async Task<AjaxResult> ImportChangeOrder(IFormFile file, string ProjectId, string BuildingNumberID)
        {

            List<string> StandardParts = new List<string>();
            string ReturnValue = string.Empty;
            bool flag = true;
            try
            {

                //通过项目楼号判断清单是否存在
                var data = _pB_DesignListBus.GetQueryable<PB_DesignList>().Where(m => m.ProjectId == ProjectId && m.BuildingNumberID == BuildingNumberID && m.Type == 1).FirstOrDefault();
                //获取清单明细物料
                var DetailList = _pB_DesignListBus.GetQueryableOK<PB_DesignDetail>().Where(m=>m.ParentId==data.Id).ToList();
                if (data == null)
                {
                    return Error(ReturnValue = "清单不存在！");
                }
                if (data.ExamineStatus < 2 || data.ExamineStatus == 4)
                {
                    return Error(ReturnValue = "设计清单当前状态不允许变更导入！");
                }
                //查询设计清单明细排序最大的值
                //var datadetail = _pB_DesignDetailBus.GetQueryable<PB_DesignDetail>().Where(x => x.ParentId == data.Id).OrderByDescending(x => x.Slot).FirstOrDefault();
                //获取现有设计清单明细
                //List<PB_DesignDetail> DesignDetailList = _pB_DesignListBus.GetQueryable<PB_DesignDetail>().Where(m => m.ParentId == data.Id).ToList();

                #region 检查文件
                string fileExt = Path.GetExtension(file.FileName).ToLower();
                List<DesignImportDTO> Importlist = new List<DesignImportDTO>();
                List<PB_Material> MaterialList = new List<PB_Material>();
                var DataInfo = new List<PB_DesignDetail>();
                List<Base_UnitItems> dicMaterialUnit = new List<Base_UnitItems>();
                List<PB_DesignDetail> UpdatepB_Designs = new List<PB_DesignDetail>();
                MemoryStream ms = new MemoryStream();
                file.CopyTo(ms);
                ms.Seek(0, SeekOrigin.Begin);
                IWorkbook book;
                if (fileExt == ".xlsx")
                {
                    book = new XSSFWorkbook(ms);
                }
                else if (fileExt == ".xls")
                {
                    book = new HSSFWorkbook(ms);
                }
                else
                {
                    book = null;
                }
                ISheet sheet = book.GetSheetAt(0);

                int CountRow = sheet.LastRowNum + 1;//获取总行数
                if (CountRow - 1 == 0)
                {
                    return Error("Excel列表数据项为空!");
                }
                #endregion
                #region 循环验证
                flag = true;
                #endregion
                List<string> listMaterialUnit = new List<string>();
                bool? trueOrfalse = true;
                if (flag)
                {
                    //int index = Convert.ToInt32(datadetail.Slot);
                    for (int i = 0; i < CountRow; i++)//
                    {
                        var row = sheet.GetRow(i);
                        if (i < 7)
                        {
                            if (i == 0)
                            {
                                
                                    string ExcelKey = row.GetCell(0).ToString();
                                    if (ExcelKey != "QspZCjj7iEGrPWQUg4eqyQ")
                                    {
                                        return Error("导入为非法模板,请下载系统标准模板整理数据导入!");
                                    }
                                
                            }
                            if (i == 1)
                            {
                                //绑定项目名称
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    //data.Name = row.GetCell(0).ToString();
                                }
                            }
                            if (i == 2)
                            {
                                //绑定工程名称/ID/创建者ID
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    //data.ProjectName = row.GetCell(0).ToString().Split("：")?[1];
                                }
                                //绑定机位数量
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    data.StandsNums = row.GetCell(6).ToString().Split("：")?[1];
                                }
                            }
                            if (i == 3)
                            {
                                //绑定工程地址
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    data.ProjectAddress = row.GetCell(0).ToString().Split("：")?[1];
                                }
                                //提升方式
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    data.LiftingMode = row.GetCell(6).ToString().Split("：")[1];
                                }
                            }
                            if (i == 4)
                            {
                                //绑定业务负责人
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    data.Salesman = row.GetCell(0).ToString().Split("：")[1];
                                }
                                //绑定业务负责人电话号码
                                if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                                {
                                    data.Phone0 = row.GetCell(2).ToString().Split("：")[1];
                                }
                                //架体步高
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    data.StepHeight = row.GetCell(6).ToString().Split("：")[1];
                                }
                            }
                            if (i == 5)
                            {
                                //绑定接货负责人
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    data.Receiver = row.GetCell(0).ToString().Split("：")[1];
                                }
                                //绑定接货负责人联系电话
                                if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                                {
                                    data.Phone1 = row.GetCell(2).ToString().Split("：")[1];
                                }
                                //架体颜色
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    data.Color = row.GetCell(6).ToString().Split("：")[1];
                                }
                            }
                            if (i == 6)
                            {
                                //进场时间
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    string tmpDate = row.GetCell(0).ToString().Split('：')[1];
                                    if (!tmpDate.IsNullOrEmpty())
                                    {
                                        if (tmpDate.IndexOf("/") > -1)
                                        {
                                            tmpDate = tmpDate.Replace("/", "-");
                                        }
                                        data.InDate = Convert.ToDateTime(tmpDate);
                                    }
                                    else
                                    {
                                        data.InDate = DateTime.Now;
                                    }
                                }
                                //下单时间
                                if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                                {
                                    var dateInfo = row.GetCell(2).ToString().Split('：')[1];
                                    if (!dateInfo.IsNullOrEmpty())
                                    {
                                        if (dateInfo.IndexOf("/") > -1)
                                        {
                                            dateInfo = dateInfo.Replace("/", "-");
                                        }
                                        data.OrderDate = Convert.ToDateTime(dateInfo);
                                    }
                                    else
                                    {
                                        data.OrderDate = DateTime.Now;
                                    }
                                }
                                //网板样式
                                if (row?.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    data.Style = row.GetCell(6).ToString().Split('：')[1];
                                }
                            }
                        }
                        if (i == 8)
                        {
                            if (row.GetCell(1) != null && row.GetCell(1).ToString().Trim().Length > 0)
                            {
                                data.Remarks += row.GetCell(1).ToString().Split("：")[1];
                            }
                        }
                        if (i > 8)
                        {
                            if (!row.GetCell(0).IsNullOrEmpty())
                            {
                                if (row.GetCell(1).ToString() == "增加")
                                {
                                    trueOrfalse = true;
                                    continue;
                                }
                                else if (row.GetCell(1).ToString() == "减少")
                                {
                                    trueOrfalse = false;
                                    continue;
                                }
                                DesignImportDTO desdata = new DesignImportDTO();
                                if (row.GetCell(1) != null && row.GetCell(1).ToString().Trim().Length > 0)
                                {
                                    //index++;
                                    desdata.Slot = IdHelper.GetId();
                                    desdata.Name = row.GetCell(1).ToString();
                                }
                                if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                                {
                                    desdata.ProductCode = row.GetCell(2).ToString();
                                }
                                if (row.GetCell(3) != null && row.GetCell(3).ToString().Trim().Length > 0)
                                {
                                    if (row.GetCell(3).CellType == CellType.Formula)
                                    {
                                        row.GetCell(3).SetCellType(CellType.String);
                                        desdata.Weight = Convert.ToDouble(row.GetCell(3).StringCellValue);
                                    }
                                    else
                                    {
                                        desdata.Weight = PB_DesignListController.IsNumber(row.GetCell(3).ToString()) == true ? Convert.ToDouble(row.GetCell(3).ToString()) : 0;
                                    }
                                }
                                if (row.GetCell(4) != null && row.GetCell(4).ToString().Trim().Length > 0)
                                {
                                    desdata.unitName = row.GetCell(4).ToString();
                                }
                                if (row.GetCell(5) != null && row.GetCell(5).ToString().Trim().Length > 0)
                                {
                                    desdata.Standard = row.GetCell(5).StringCellValue.ToString();
                                    if (row.GetCell(5).StringCellValue.ToString() == "是")
                                    {
                                        listMaterialUnit.Add(row.GetCell(4).ToString());
                                    }
                                }
                                //绑定设计数量
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    if (row.GetCell(6).CellType == CellType.Formula)
                                    {
                                        row.GetCell(6).SetCellType(CellType.String);
                                        desdata.Nums = Convert.ToDouble(row.GetCell(6).StringCellValue);
                                    }
                                    else
                                    {
                                        desdata.Nums = PB_DesignListController.IsNumber(row.GetCell(6).ToString()) == true ? Convert.ToDouble(row.GetCell(6).ToString()) : 0;
                                    }
                                }
                                //绑定实发数量
                                if (row.GetCell(7) != null && row.GetCell(7).ToString().Trim().Length > 0)
                                {
                                    if (row.GetCell(7).CellType == CellType.Formula)
                                    {
                                        row.GetCell(7).SetCellType(CellType.String);
                                        desdata.RealNums = Convert.ToDouble(row.GetCell(7).StringCellValue);
                                    }
                                    else
                                    {
                                        if (row.GetCell(7).ToString().Trim() == "相同")
                                        {
                                            desdata.RealNums = desdata.Nums;
                                        }
                                        else
                                        {
                                            if (row.GetCell(7).CellType == CellType.Formula)
                                            {
                                                row.GetCell(7).SetCellType(CellType.String);
                                                desdata.RealNums = Convert.ToDouble(row.GetCell(7).StringCellValue);
                                            }
                                            else
                                            {
                                                desdata.RealNums = PB_DesignListController.IsNumber(row.GetCell(7).ToString()) == true ? Convert.ToDouble(row.GetCell(7).ToString()) : 0;
                                            }
                                        }
                                    }
                                }
                                //绑定备注
                                if (row.GetCell(8) != null && row.GetCell(8).ToString().Trim().Length > 0)
                                {
                                    desdata.Remarks = row.GetCell(8).ToString();
                                }
                                desdata.TorF = trueOrfalse;
                                Importlist.Add(desdata);
                            }
                        }
                    }
                    var listMaterialTypSpace = Importlist.Select(s => s.ProductCode).ToList();
                    //获取物料主键ID                    
                    var dicMaterialSpace = _pB_MaterialBus.GetQueryableByProductCode<PB_ProductCodeIdsDTO>(listMaterialTypSpace);

                    //物料分类
                    var materialTypedata = _pB_MaterialBus.GetDesignListMaterialTypeAsync();
                    string matercode = materialTypedata.MaterialCode;

                    //单位
                    if (listMaterialUnit.Count > 0)
                    {
                        dicMaterialUnit = _pB_MaterialBus.GetUnitItems<Base_UnitItems>(listMaterialUnit);
                    }

                    string SpaceString = "";
                    //插入子表
                    foreach (var item in Importlist)
                    {
                        if (item.TorF == true)
                        {
                            PB_DesignDetail DesignDetail = new PB_DesignDetail();
                            DesignDetail.Id = IdHelper.GetId();
                            DesignDetail.ParentId = data.Id;
                            DesignDetail.Nums = Convert.ToDouble(item.Nums);
                            DesignDetail.RealNums = Convert.ToDouble(item.RealNums);
                            DesignDetail.ShipmentNums = 0;
                            DesignDetail.CreatorId = _operator?.UserId;
                            DesignDetail.CreateTime = DateTime.Now;
                            DesignDetail.State = 1;
                            DesignDetail.FifteenStep = DesignDetail.RealNums;
                            DesignDetail.VersionNumber = 0;
                            DesignDetail.ShipmentNums = 0;
                            DesignDetail.Slot = item.Slot;
                            DesignDetail.Remarks = item.Remarks;
                            if (item.Standard == "是")
                            {
                                //通过成品编号绑定物料主键ID
                                if (dicMaterialSpace.Any(s => s.ProductCode == item.ProductCode.Trim()))
                                {
                                    StandardParts.Add(item.ProductCode);
                                    //当前成品编码存在
                                    DesignDetail.MaterialId = dicMaterialSpace.FirstOrDefault(x => x.ProductCode == item.ProductCode.Trim()).Id;
                                }
                                else
                                {
                                    PB_Material madata = new PB_Material();
                                    string maid = IdHelper.GetId();
                                    madata.Id = maid;
                                    madata.DeptId = _operator.Property?.FactoryId;
                                    madata.Name = item.Name;
                                    if (matercode.IsNullOrEmpty())
                                    {
                                        madata.Code = "16.000001";
                                        matercode = "000001";
                                    }
                                    else
                                    {
                                        if (matercode.Length == 6)
                                        {
                                            string code = "000000" + (Convert.ToInt32(matercode) + 1);
                                            madata.Code = "16." + code.Substring(code.Length - 6, 6);
                                            matercode = code.Substring(code.Length - 6, 6);
                                        }
                                        else
                                        {
                                            madata.Code = "16." + (Convert.ToInt32(matercode) + 1);
                                            matercode = (Convert.ToInt32(matercode) + 1).ToString();
                                        }
                                    }
                                    madata.ProductCode = item.ProductCode;
                                    madata.Spec = item.ProductCode;
                                    madata.SimpleName = item.Name;
                                    madata.MaterialTypeId = materialTypedata.Id;
                                    if (dicMaterialUnit.Any(s => s.Name == item.unitName.Trim()))
                                    {
                                        //当前成品编码存在
                                        madata.UnitGroupId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).GroupId;
                                        madata.storageId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).Id;
                                        madata.saleId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).Id;
                                        madata.purchaseId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).Id;
                                        madata.productionId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).Id;
                                    }
                                    madata.Weight = Convert.ToDouble(item.Weight);
                                    madata.IsProduct = true;
                                    madata.IsMine = true;
                                    madata.IsBuy = false;
                                    madata.HasRef = false;
                                    madata.IsVirtualkey = false;
                                    madata.IsLabour = false;
                                    madata.IsNumber = true;
                                    madata.IsKeyprice = false;
                                    madata.IsCruxMaterial = false;
                                    madata.IsQuantity = false;
                                    madata.IsExtensiveProduction = false;
                                    madata.IsBecode = true;
                                    madata.PackageNum = 1;
                                    madata.IsSingle = false;
                                    madata.IsStandard = false;
                                    madata.IsSpec = false;
                                    madata.CreateTime = DateTime.Now;
                                    madata.CreatorId = _operator.UserId;
                                    madata.Deleted = false;
                                    madata.UsageStatus = true;
                                    MaterialList.Add(madata);
                                    DesignDetail.MaterialId = maid;
                                }
                            }
                            else
                            {
                                //通过成品编号绑定物料主键ID
                                if (dicMaterialSpace.Any(s => s.ProductCode == item.ProductCode.Trim()))
                                {
                                    //当前成品编码存在
                                    DesignDetail.MaterialId = dicMaterialSpace.FirstOrDefault(x => x.ProductCode == item.ProductCode.Trim()).Id;
                                }
                                else
                                {
                                    //当前成品编码不存在
                                    SpaceString += "[编号：" + item.ProductCode.Trim() + "],";
                                }
                            }
                            DataInfo.Add(DesignDetail);
                        }
                        else if (item.TorF == false)
                        {
                            PB_DesignDetail DesignDetail = new PB_DesignDetail();
                            //通过成品编号绑定物料主键ID
                            if (dicMaterialSpace.Any(s => s.ProductCode == item.ProductCode.Trim()))
                            {
                                //当前成品编码存在
                                DesignDetail.MaterialId = dicMaterialSpace.FirstOrDefault(x => x.ProductCode == item.ProductCode.Trim()).Id;
                            }
                            else
                            {
                                //当前成品编码不存在
                                SpaceString += "[编号：" + item.ProductCode.Trim() + "],";
                            }
                            DesignDetail.Id = IdHelper.GetId();
                            DesignDetail.ParentId = data.Id;
                            DesignDetail.Nums = Convert.ToDouble(item.Nums);
                            DesignDetail.RealNums = Convert.ToDouble(item.RealNums);
                            DesignDetail.ShipmentNums = 0;
                            DesignDetail.CreatorId = _operator?.UserId;
                            DesignDetail.CreateTime = DateTime.Now;
                            DesignDetail.State = 2;
                            DesignDetail.VersionNumber = 0;
                            DesignDetail.ShipmentNums = 0;
                            DesignDetail.Slot = item.Slot;
                            DesignDetail.Remarks = item.Remarks;
                            DataInfo.Add(DesignDetail);
                        }
                    }
                    //成品编码不存在则return
                    if (!string.IsNullOrEmpty(SpaceString))
                    {
                        return Error(ReturnValue = "成品编码不存在！" + SpaceString.Substring(0, SpaceString.Length - 1));
                    }

                    List<PB_DesignDetail> DesignDetailinfo = new List<PB_DesignDetail>();
                    //获取变更版本
                    PB_DesignListVersion Versiondata = _pB_DesignListBus.GetDesignListVersionAsync(data.Id);
                    //int inti = Importlist.Count;

                    foreach (var item in DataInfo)
                    {
                        PB_DesignDetail pbdata = new PB_DesignDetail();
                        pbdata.Id = item.Id;
                        pbdata.ParentId = item.ParentId;
                        pbdata.MaterialId = item.MaterialId;
                        pbdata.Nums = item.Nums;
                        pbdata.RealNums = item.RealNums;
                        pbdata.ShipmentNums = item.ShipmentNums;
                        pbdata.Remarks = item.Remarks;
                        pbdata.CreateTime = item.CreateTime;
                        pbdata.CreatorId = item.CreatorId;
                        pbdata.Deleted = item.Deleted;
                        pbdata.Weight = item.Weight;
                        pbdata.VersionNumber = Versiondata.VersionNumber;
                        pbdata.State = item.State;
                        pbdata.ReplaceId = pbdata.ReplaceId;
                        pbdata.Slot = item.Slot;
                        if (pbdata.State==1) {
                            pbdata.FifteenStep = pbdata.RealNums;
                        }
                        if (pbdata.State == 2)
                        {

                            double? jianshaonuns=0;

                            var dbmater =  _pB_DesignDetailBus.GetQueryable<PB_DesignDetail>().Where(m => m.ParentId == item.ParentId && m.MaterialId == item.MaterialId ).FirstOrDefault();
                            if (dbmater==null) {
                                var mater = _pB_DesignDetailBus.GetQueryable<PB_Material>().Where(s=>s.Id==item.MaterialId).FirstOrDefault();
                                return Error(ReturnValue = $"{mater.Name}不存在设计清单当中请先进行补加!");
                            }
                             //获取第十五步补加的信息
                             var pB_DesignDetail2 = DetailList.Where(m => m.State < 2 && m.MaterialId == pbdata.MaterialId).ToList();
                            var Wuliao = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FifteenStep = x.Sum(p => p.FifteenStep) }).FirstOrDefault();
                            //获取第十五步变更减少sum的总数
                            var pB_DesignDetailDelete = DetailList.Where(m => m.State == 2 && m.MaterialId == pbdata.MaterialId).ToList();
                            var Wuliao2 = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FifteenStep = x.Sum(p => p.FifteenStep) }).FirstOrDefault();
                         
                          

                            //获取第十五步数量效验
                            var FifteenStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FifteenStep = x.Sum(p => p.FifteenStep) }).FirstOrDefault();
                            var FifteenStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FifteenStepReady = x.Sum(p => p.FifteenStepReady) }).FirstOrDefault();
                            var FifteenStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FifteenStep = x.Sum(p => p.FifteenStep) }).FirstOrDefault();
                            var FifteenStepDeleteReady = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FifteenStepReady = x.Sum(p => p.FifteenStepReady) }).FirstOrDefault();
                            double? numsFifteenStep = (FifteenStepNums.FifteenStep - FifteenStepReady.FifteenStepReady) - ((FifteenStepDeleteNums == null ? 0 : FifteenStepDeleteNums.FifteenStep) - (FifteenStepDeleteReady == null ? 0 : FifteenStepDeleteReady.FifteenStepReady));
                            jianshaonuns += (Wuliao == null ? 0 : Wuliao.FifteenStep) - (Wuliao2 == null ? 0 : Wuliao2.FifteenStep);
                            jianshaonuns += (FifteenStepNums == null ? 0 : FifteenStepNums.FifteenStep) - (FifteenStepDeleteNums == null ? 0 : FifteenStepDeleteNums.FifteenStep);


                            //获取第十步数量效验
                            var TenStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, TenStep = x.Sum(p => p.TenStep) }).FirstOrDefault();
                            var TenStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, TenStepReady = x.Sum(p => p.TenStepReady) }).FirstOrDefault();
                            var TenStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, TenStep = x.Sum(p => p.TenStep) }).FirstOrDefault();
                            var TenStepDeleteReady = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, TenStepReady = x.Sum(p => p.TenStepReady) }).FirstOrDefault();
                            double? numsTenStep = ((TenStepNums == null ? 0 : TenStepNums.TenStep) - (TenStepReady == null ? 0 : TenStepReady.TenStepReady)) - ((TenStepDeleteNums == null ? 0 : TenStepDeleteNums.TenStep) - (TenStepDeleteReady == null ? 0 : TenStepDeleteReady.TenStepReady));
                            ;


                            //获取第九步数量效验 
                            var NineStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, NineStep = x.Sum(p => p.NineStep) }).FirstOrDefault();
                            var NineStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, NineStepReady = x.Sum(p => p.NineStepReady) }).FirstOrDefault();
                            var NineStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, NineStep = x.Sum(p => p.NineStep) }).FirstOrDefault();
                            var NineStepReadyDelete = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, NineStepReady = x.Sum(p => p.NineStepReady) }).FirstOrDefault();
                            double? numsNineStep = ((NineStepNums == null ? 0 : NineStepNums.NineStep) - (NineStepReady == null ? 0 : NineStepReady.NineStepReady)) - ((NineStepDeleteNums == null ? 0 : NineStepDeleteNums.NineStep) - (NineStepReadyDelete == null ? 0 : NineStepReadyDelete.NineStepReady));
                  

                            //获取第八步数量效验
                            var EightStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, EightStep = x.Sum(p => p.EightStep) }).FirstOrDefault();
                            var EightStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, EightStepReady = x.Sum(p => p.EightStepReady) }).FirstOrDefault();
                            var EightStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, EightStep = x.Sum(p => p.EightStep) }).FirstOrDefault();
                            var EightStepReadyDelete = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, EightStepReady = x.Sum(p => p.EightStepReady) }).FirstOrDefault();
                            double? numsEightStep = ((EightStepNums == null ? 0 : EightStepNums.EightStep) - (EightStepReady == null ? 0 : EightStepReady.EightStepReady)) - ((EightStepDeleteNums == null ? 0 : EightStepDeleteNums.EightStep) - (EightStepReadyDelete == null ? 0 : EightStepReadyDelete.EightStepReady));
                         

                            //获取七步数量效验
                            var SevenStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, SevenStep = x.Sum(p => p.SevenStep) }).FirstOrDefault();
                            var SevenStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, SevenStepReady = x.Sum(p => p.SevenStepReady) }).FirstOrDefault();
                            var SevenStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, SevenStep = x.Sum(p => p.SevenStep) }).FirstOrDefault();
                            var SevenStepReadyDelete = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, SevenStepReady = x.Sum(p => p.SevenStepReady) }).FirstOrDefault();
                            double? numsSevenStep = ((SevenStepNums == null ? 0 : SevenStepNums.SevenStep) - (SevenStepReady == null ? 0 : SevenStepReady.SevenStepReady)) - ((SevenStepDeleteNums == null ? 0 : SevenStepDeleteNums.SevenStep) - (SevenStepReadyDelete == null ? 0 : SevenStepReadyDelete.SevenStepReady));
                           

                            //获取第六步数量效验
                            var SixStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, SixStep = x.Sum(p => p.SixStep) }).FirstOrDefault();
                            var SixStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, SixStepReady = x.Sum(p => p.SixStepReady) }).FirstOrDefault();
                            var SixStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, SixStep = x.Sum(p => p.SixStep) }).FirstOrDefault();
                            var SixStepReadyDelete = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, SixStepReady = x.Sum(p => p.SixStepReady) }).FirstOrDefault();
                            double? numsSixStep = ((SevenStepNums == null ? 0 : SixStepNums.SixStep) - (SixStepReady == null ? 0 : SixStepReady.SixStepReady)) - ((SevenStepDeleteNums == null ? 0 : SixStepDeleteNums.SixStep) - (SixStepReadyDelete == null ? 0 : SixStepReadyDelete.SixStepReady));
                          

                            //获取第五步数量效验
                            var FiveStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FiveStep = x.Sum(p => p.FiveStep) }).FirstOrDefault();
                            var FiveStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FiveStepReady = x.Sum(p => p.FiveStepReady) }).FirstOrDefault();
                            var FiveStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FiveStep = x.Sum(p => p.FiveStep) }).FirstOrDefault();
                            var FiveStepReadyDelete = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FiveStepReady = x.Sum(p => p.FiveStepReady) }).FirstOrDefault();
                            double? numsFiveStep = ((FiveStepNums == null ? 0 : FiveStepNums.FiveStep) - (FiveStepReady == null ? 0 : FiveStepReady.FiveStepReady)) - ((FiveStepDeleteNums == null ? 0 : FiveStepDeleteNums.FiveStep) - (FiveStepReadyDelete == null ? 0 : FiveStepReadyDelete.FiveStepReady));
                       
                            //获取第四步数量效验
                            var FourStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FourStep = x.Sum(p => p.FourStep) }).FirstOrDefault();
                            var FourStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FourStepReady = x.Sum(p => p.FourStepReady) }).FirstOrDefault();
                            var FourStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FourStep = x.Sum(p => p.FourStep) }).FirstOrDefault();
                            var FourStepReadyDelete = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FourStepReady = x.Sum(p => p.FourStepReady) }).FirstOrDefault();
                            double? numsFourStep = ((FourStepNums == null ? 0 : FourStepNums.FourStep) - (FourStepReady == null ? 0 : FourStepReady.FourStepReady)) - ((FiveStepDeleteNums == null ? 0 : FourStepDeleteNums.FourStep) - (FourStepReadyDelete == null ? 0 : FourStepReadyDelete.FourStepReady));
                            

                            //获取第三步数量效验
                            var ThreeStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, ThreeStep = x.Sum(p => p.ThreeStep) }).FirstOrDefault();
                            var ThreeStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, ThreeStepReady = x.Sum(p => p.ThreeStepReady) }).FirstOrDefault();
                            var ThreeStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, ThreeStep = x.Sum(p => p.ThreeStep) }).FirstOrDefault();
                            var ThreeStepReadyDelete = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, ThreeStepReady = x.Sum(p => p.ThreeStepReady) }).FirstOrDefault();
                            double? numsThreeStep = ((FourStepNums == null ? 0 : ThreeStepNums.ThreeStep) - (ThreeStepReady == null ? 0 : ThreeStepReady.ThreeStepReady)) - ((ThreeStepDeleteNums == null ? 0 : ThreeStepDeleteNums.ThreeStep) - (ThreeStepReadyDelete == null ? 0 : ThreeStepReadyDelete.ThreeStepReady));
                       
                            //获取第二步数量效验
                            var TwoStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, TwoStep = x.Sum(p => p.TwoStep) }).FirstOrDefault();
                            var TwoStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, TwoStepReady = x.Sum(p => p.TwoStepReady) }).FirstOrDefault();
                            var TwoStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, TwoStep = x.Sum(p => p.TwoStep) }).FirstOrDefault();
                            var TwoStepReadyDelete = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, TwoStepReady = x.Sum(p => p.TwoStepReady) }).FirstOrDefault();
                            double? numsTwoStep = ((TwoStepNums == null ? 0 : TwoStepNums.TwoStep) - (TwoStepReady == null ? 0 : TwoStepReady.TwoStepReady)) - ((TwoStepDeleteNums == null ? 0 : TwoStepDeleteNums.TwoStep) - (TwoStepReadyDelete == null ? 0 : TwoStepReadyDelete.TwoStepReady));
                           
                            //获取第一步数量效验
                            var OneStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, OneStep = x.Sum(p => p.OneStep) }).FirstOrDefault();
                            var OneStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, OneStepReady = x.Sum(p => p.OneStepReady) }).FirstOrDefault();
                            var OneStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, OneStep = x.Sum(p => p.OneStep) }).FirstOrDefault();
                            var OneStepReadyDelete = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, OneStepReady = x.Sum(p => p.OneStepReady) }).FirstOrDefault();
                            double? numsOneStep = ((OneStepNums == null ? 0 : OneStepNums.OneStep) - (OneStepReady == null ? 0 : OneStepReady.OneStepReady)) - ((OneStepDeleteNums == null ? 0 : OneStepDeleteNums.OneStep) - (OneStepReadyDelete == null ? 0 : OneStepReadyDelete.OneStepReady));
                           

                            double? CalculateSum = numsTenStep + numsNineStep + numsEightStep + numsSevenStep + numsSixStep + numsFiveStep + numsFourStep + numsThreeStep + numsTwoStep + numsOneStep;
                            if (CalculateSum < pbdata.RealNums)
                            {
                                double? jian = jianshaonuns + CalculateSum;
                                double? nums = (pbdata.RealNums - CalculateSum);
                                var jiannums = numsFifteenStep + CalculateSum;
                               
                                if (jian <pbdata.RealNums)
                                {
                                    var mater = _pB_DesignDetailBus.GetQueryable<PB_Material>().Where(s => s.Id == item.MaterialId).FirstOrDefault();
                                    return Error(ReturnValue = $"{mater.Name}减少数量超出或已备货");
                                }
                                else {
                                    pbdata.FifteenStep = nums;
                                }
                                

                            }
                            double? Value = numsTenStep;
                            double? surplus = 0;
                            surplus = Value - pbdata.RealNums;
                            if (surplus < 0)
                            {

                                if (numsTenStep > 0 && surplus < 0)
                                {
                                    surplus = numsTenStep >= -surplus ? -surplus : surplus + numsTenStep;
                                    pbdata.TenStep = surplus >= 0 ? surplus : numsTenStep;

                                }

                                if (numsNineStep > 0 && surplus < 0)
                                {

                                    surplus = numsNineStep >= -surplus ? -surplus : surplus + numsNineStep;
                                    pbdata.NineStep = surplus >= 0 ? surplus : numsNineStep;

                                }

                                if (numsEightStep > 0 && surplus < 0)
                                {

                                    surplus = numsEightStep >= -surplus ? -surplus : surplus + numsEightStep;
                                    pbdata.EightStep = surplus >= 0 ? surplus : numsEightStep;

                                }
                                if (numsSevenStep > 0 && surplus < 0)
                                {

                                    surplus = numsSevenStep >= -surplus ? -surplus : surplus + numsSevenStep;
                                    pbdata.SevenStep = surplus >= 0 ? surplus : numsSevenStep;

                                }
                                if (numsSixStep > 0 && surplus < 0)
                                {

                                    surplus = numsSixStep >= -surplus ? -surplus : surplus + numsSixStep;
                                    pbdata.SixStep = surplus >= 0 ? surplus : numsSixStep;
                                }
                                if (numsFiveStep > 0 && surplus < 0)
                                {

                                    surplus = numsFiveStep >= -surplus ? -surplus : surplus + numsFiveStep;
                                    pbdata.FiveStep = surplus >= 0 ? surplus : numsFiveStep;
                                }
                                if (numsFourStep > 0 && surplus < 0)
                                {
                                    surplus = numsFourStep >= -surplus ? -surplus : surplus + numsFourStep;
                                    pbdata.FourStep = surplus >= 0 ? surplus : numsFourStep;
                                }
                                if (numsThreeStep > 0 && surplus < 0)
                                {
                                    surplus = numsThreeStep >= -surplus ? -surplus : surplus + numsThreeStep;
                                    pbdata.ThreeStep = surplus >= 0 ? surplus : numsThreeStep;
                                }
                                if (numsTwoStep > 0 && surplus < 0)
                                {
                                    surplus = numsTwoStep >= -surplus ? -surplus : surplus + numsTwoStep;
                                    pbdata.TwoStep = surplus >= 0 ? surplus : numsTwoStep;
                                }
                                if (numsOneStep > 0 && surplus < 0)
                                {
                                    surplus = numsOneStep >= -surplus ? -surplus : surplus + numsOneStep;
                                    pbdata.OneStep = surplus >= 0 ? surplus : numsOneStep;
                                }

                            }
                        }
                            DesignDetailinfo.Add(pbdata);
                    }
                    // await _pB_DesignListBus.PushChangMessage(data.Id);
                    await _pB_DesignDetailBus.AddDataExlAsync(DesignDetailinfo, data.Id);
                    await _pB_MaterialBus.AddDataExlAsync(MaterialList, _redisOptions, _mongodbOptions);
                    //await _pB_DesignListBus.UpdateData(UpdatepB_Designs);
                    await _pB_DesignListBus.UpdateDataAsync(data);
                    await _pB_DesignListBus.updatastate(data.Id);
                    if (StandardParts.Count > 0)
                    {
                        string str = string.Join(",", StandardParts);
                        ReturnValue = $"数据导入成功,共导入{DataInfo.Count}条数据,本次包含非标件。";
                    }
                    else
                    {
                        ReturnValue = $"数据导入成功,共导入{DataInfo.Count}条数据。";
                    }
                }
                else if (!flag)
                {
                    return Error(ReturnValue = "数据存在问题！" + ReturnValue);
                }
            }
            catch (Exception ex)
            {
                return Error("文件导入失败请检查文件内容");
            }

            return Success(ReturnValue);


        }
        /// <summary>
        /// 料台变更导入
        /// </summary>
        /// <param name="file"></param>
        /// <param name="ProjectId"></param>
        /// <param name="BuildingNumberId"></param>
        /// <returns></returns>
        public async Task<AjaxResult> MaterialTableChangOrder(IFormFile file,string ProjectId,string BuildingNumberID) {

            List<string> StandardParts = new List<string>();
            string ReturnValue = string.Empty;
            bool flag = true;
            try
            {

                //通过项目楼号判断清单是否存在
                var data = _pB_DesignListBus.GetQueryable<PB_DesignList>().Where(m => m.ProjectId == ProjectId && m.BuildingNumberID == BuildingNumberID && m.Type == 2).FirstOrDefault();
                if (data == null)
                {
                    return Error(ReturnValue = "料台清单不存在！");
                }
                //获取清单明细物料
                var DetailList = _pB_DesignListBus.GetQueryableOK<PB_DesignDetail>().Where(m => m.ParentId == data.Id).ToList();
               
                if (data.ExamineStatus < 2 || data.ExamineStatus == 4)
                {
                    return Error(ReturnValue = "料台清单当前状态不允许变更导入！");
                }

                #region 检查文件
                string fileExt = Path.GetExtension(file.FileName).ToLower();
                List<DesignImportDTO> Importlist = new List<DesignImportDTO>();
                List<PB_Material> MaterialList = new List<PB_Material>();
                var DataInfo = new List<PB_DesignDetail>();
                List<Base_UnitItems> dicMaterialUnit = new List<Base_UnitItems>();
                List<PB_DesignDetail> UpdatepB_Designs = new List<PB_DesignDetail>();
                MemoryStream ms = new MemoryStream();
                file.CopyTo(ms);
                ms.Seek(0, SeekOrigin.Begin);
                IWorkbook book;
                if (fileExt == ".xlsx")
                {
                    book = new XSSFWorkbook(ms);
                }
                else if (fileExt == ".xls")
                {
                    book = new HSSFWorkbook(ms);
                }
                else
                {
                    book = null;
                }
                ISheet sheet = book.GetSheetAt(0);

                int CountRow = sheet.LastRowNum + 1;//获取总行数
                if (CountRow - 1 == 0)
                {
                    return Error("Excel列表数据项为空!");
                }
                #endregion
                #region 循环验证
                flag = true;
                #endregion
                List<string> listMaterialUnit = new List<string>();
                bool? trueOrfalse = true;
                if (flag)
                {
                    //int index = Convert.ToInt32(datadetail.Slot);
                    for (int i = 0; i < CountRow; i++)//
                    {
                        var row = sheet.GetRow(i);
                        if (i < 6)
                        {
                            if (i == 0)
                            {
                                   //查询模板
                                    string ExcelKey = row.GetCell(0).ToString();
                                    if (ExcelKey != "QspZCjj7iEGrPWQUg4eqyQ")
                                    {
                                        return Error("导入为非法模板,请下载系统标准模板整理数据导入!");
                                    }
                               
                            }
                            if (i == 1)
                            {
                                //绑定项目名称
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    //data.Name = row.GetCell(0).ToString();
                                }
                            }
                            if (i == 2)
                            {
                                //绑定工程名称/ID/创建者ID
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    //data.ProjectName = row.GetCell(0).ToString().Split("：")?[1];
                                }
                                //绑定机位数量
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    data.StandsNums = row.GetCell(6).ToString().Split("：")?[1];
                                }
                            }
                            if (i == 3)
                            {
                                //绑定工程地址
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    data.ProjectAddress = row.GetCell(0).ToString().Split("：")?[1];
                                }
                                //提升方式
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    data.LiftingMode = row.GetCell(6).ToString().Split("：")[1];
                                }
                            }
                            if (i == 4)
                            {
                                //绑定业务负责人
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    data.Salesman = row.GetCell(0).ToString().Split("：")[1];
                                }
                                //绑定业务负责人电话号码
                                if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                                {
                                    data.Phone0 = row.GetCell(2).ToString().Split("：")[1];
                                }
                                //架体步高
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    data.StepHeight = row.GetCell(6).ToString().Split("：")[1];
                                }
                            }
                            if (i == 5)
                            {
                                //绑定接货负责人
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    data.Receiver = row.GetCell(0).ToString().Split("：")[1];
                                }
                                //绑定接货负责人联系电话
                                if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                                {
                                    data.Phone1 = row.GetCell(2).ToString().Split("：")[1];
                                }
                                //架体颜色
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    data.Color = row.GetCell(6).ToString().Split("：")[1];
                                }
                            }
                            if (i == 6)
                            {
                                //进场时间
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    string tmpDate = row.GetCell(0).ToString().Split('：')[1];
                                    if (!tmpDate.IsNullOrEmpty())
                                    {
                                        if (tmpDate.IndexOf("/") > -1)
                                        {
                                            tmpDate = tmpDate.Replace("/", "-");
                                        }
                                        data.InDate = Convert.ToDateTime(tmpDate);
                                    }
                                    else
                                    {
                                        data.InDate = DateTime.Now;
                                    }
                                }
                                //下单时间
                                if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                                {
                                    var dateInfo = row.GetCell(2).ToString().Split('：')[1];
                                    if (!dateInfo.IsNullOrEmpty())
                                    {
                                        if (dateInfo.IndexOf("/") > -1)
                                        {
                                            dateInfo = dateInfo.Replace("/", "-");
                                        }
                                        data.OrderDate = Convert.ToDateTime(dateInfo);
                                    }
                                    else
                                    {
                                        data.OrderDate = DateTime.Now;
                                    }
                                }
                                //网板样式
                                if (row?.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    data.Style = row.GetCell(6).ToString().Split('：')[1];
                                }
                            }
                        }
                        if (i == 8)
                        {
                            if (row.GetCell(1) != null && row.GetCell(1).ToString().Trim().Length > 0)
                            {
                                data.Remarks += row.GetCell(1).ToString().Split("：")[1];
                            }
                        }
                        if (i > 8)
                        {
                            if (!row.GetCell(0).IsNullOrEmpty())
                            {
                                if (row.GetCell(1).ToString() == "增加")
                                {
                                    trueOrfalse = true;
                                    continue;
                                }
                                else if (row.GetCell(1).ToString() == "减少")
                                {
                                    trueOrfalse = false;
                                    continue;
                                }
                                DesignImportDTO desdata = new DesignImportDTO();
                                if (row.GetCell(1) != null && row.GetCell(1).ToString().Trim().Length > 0)
                                {
                                    //index++;
                                    desdata.Slot = IdHelper.GetId();
                                    desdata.Name = row.GetCell(1).ToString();
                                }
                                if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                                {
                                    desdata.ProductCode = row.GetCell(2).ToString();
                                }
                                if (row.GetCell(3) != null && row.GetCell(3).ToString().Trim().Length > 0)
                                {
                                    if (row.GetCell(3).CellType == CellType.Formula)
                                    {
                                        row.GetCell(3).SetCellType(CellType.String);
                                        desdata.Weight = Convert.ToDouble(row.GetCell(3).StringCellValue);
                                    }
                                    else
                                    {
                                        desdata.Weight = PB_DesignListController.IsNumber(row.GetCell(3).ToString()) == true ? Convert.ToDouble(row.GetCell(3).ToString()) : 0;
                                    }
                                }
                                if (row.GetCell(4) != null && row.GetCell(4).ToString().Trim().Length > 0)
                                {
                                    desdata.unitName = row.GetCell(4).ToString();
                                }
                                if (row.GetCell(5) != null && row.GetCell(5).ToString().Trim().Length > 0)
                                {
                                    desdata.Standard = row.GetCell(5).StringCellValue.ToString();
                                    if (row.GetCell(5).StringCellValue.ToString() == "是")
                                    {
                                        listMaterialUnit.Add(row.GetCell(4).ToString());
                                    }
                                }
                                //绑定设计数量
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    if (row.GetCell(6).CellType == CellType.Formula)
                                    {
                                        row.GetCell(6).SetCellType(CellType.String);
                                        desdata.Nums = Convert.ToDouble(row.GetCell(6).StringCellValue);
                                    }
                                    else
                                    {
                                        desdata.Nums = PB_DesignListController.IsNumber(row.GetCell(6).ToString()) == true ? Convert.ToDouble(row.GetCell(6).ToString()) : 0;
                                    }
                                }
                                //绑定实发数量
                                if (row.GetCell(7) != null && row.GetCell(7).ToString().Trim().Length > 0)
                                {
                                    if (row.GetCell(7).CellType == CellType.Formula)
                                    {
                                        row.GetCell(7).SetCellType(CellType.String);
                                        desdata.RealNums = Convert.ToDouble(row.GetCell(7).StringCellValue);
                                    }
                                    else
                                    {
                                        if (row.GetCell(7).ToString().Trim() == "相同")
                                        {
                                            desdata.RealNums = desdata.Nums;
                                        }
                                        else
                                        {
                                            if (row.GetCell(7).CellType == CellType.Formula)
                                            {
                                                row.GetCell(7).SetCellType(CellType.String);
                                                desdata.RealNums = Convert.ToDouble(row.GetCell(7).StringCellValue);
                                            }
                                            else
                                            {
                                                desdata.RealNums = PB_DesignListController.IsNumber(row.GetCell(7).ToString()) == true ? Convert.ToDouble(row.GetCell(7).ToString()) : 0;
                                            }
                                        }
                                    }
                                }
                                //绑定备注
                                if (row.GetCell(8) != null && row.GetCell(8).ToString().Trim().Length > 0)
                                {
                                    desdata.Remarks = row.GetCell(8).ToString();
                                }
                                desdata.TorF = trueOrfalse;
                                Importlist.Add(desdata);
                            }
                        }
                    }
                    var listMaterialTypSpace = Importlist.Select(s => s.ProductCode).ToList();
                    //获取物料主键ID                    
                    var dicMaterialSpace = _pB_MaterialBus.GetQueryableByProductCode<PB_ProductCodeIdsDTO>(listMaterialTypSpace);

                    //物料分类
                    var materialTypedata = _pB_MaterialBus.GetDesignListMaterialTypeAsync();
                    string matercode = materialTypedata.MaterialCode;

                    //单位
                    if (listMaterialUnit.Count > 0)
                    {
                        dicMaterialUnit = _pB_MaterialBus.GetUnitItems<Base_UnitItems>(listMaterialUnit);
                    }

                    string SpaceString = "";
                    //插入子表
                    foreach (var item in Importlist)
                    {
                        if (item.TorF == true)
                        {
                            PB_DesignDetail DesignDetail = new PB_DesignDetail();
                            DesignDetail.Id = IdHelper.GetId();
                            DesignDetail.ParentId = data.Id;
                            DesignDetail.Nums = Convert.ToDouble(item.Nums);
                            DesignDetail.RealNums = Convert.ToDouble(item.RealNums);
                            DesignDetail.ShipmentNums = 0;
                            DesignDetail.CreatorId = _operator?.UserId;
                            DesignDetail.CreateTime = DateTime.Now;
                            DesignDetail.State = 1;
                            DesignDetail.FifteenStep = DesignDetail.RealNums;
                            DesignDetail.VersionNumber = 0;
                            DesignDetail.ShipmentNums = 0;
                            DesignDetail.Slot = item.Slot;
                            DesignDetail.Remarks = item.Remarks;
                            if (item.Standard == "是")
                            {
                                //通过成品编号绑定物料主键ID
                                if (dicMaterialSpace.Any(s => s.ProductCode == item.ProductCode.Trim()))
                                {
                                    StandardParts.Add(item.ProductCode);
                                    //当前成品编码存在
                                    DesignDetail.MaterialId = dicMaterialSpace.FirstOrDefault(x => x.ProductCode == item.ProductCode.Trim()).Id;
                                }
                                else
                                {
                                    PB_Material madata = new PB_Material();
                                    string maid = IdHelper.GetId();
                                    madata.Id = maid;
                                    madata.DeptId = _operator.Property?.FactoryId;
                                    madata.Name = item.Name;
                                    if (matercode.IsNullOrEmpty())
                                    {
                                        madata.Code = "16.000001";
                                        matercode = "000001";
                                    }
                                    else
                                    {
                                        if (matercode.Length == 6)
                                        {
                                            string code = "000000" + (Convert.ToInt32(matercode) + 1);
                                            madata.Code = "16." + code.Substring(code.Length - 6, 6);
                                            matercode = code.Substring(code.Length - 6, 6);
                                        }
                                        else
                                        {
                                            madata.Code = "16." + (Convert.ToInt32(matercode) + 1);
                                            matercode = (Convert.ToInt32(matercode) + 1).ToString();
                                        }
                                    }
                                    madata.ProductCode = item.ProductCode;
                                    madata.Spec = item.ProductCode;
                                    madata.SimpleName = item.Name;
                                    madata.MaterialTypeId = materialTypedata.Id;
                                    if (dicMaterialUnit.Any(s => s.Name == item.unitName.Trim()))
                                    {
                                        //当前成品编码存在
                                        madata.UnitGroupId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).GroupId;
                                        madata.storageId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).Id;
                                        madata.saleId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).Id;
                                        madata.purchaseId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).Id;
                                        madata.productionId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).Id;
                                    }
                                    madata.Weight = Convert.ToDouble(item.Weight);
                                    madata.IsProduct = true;
                                    madata.IsMine = true;
                                    madata.IsBuy = false;
                                    madata.HasRef = false;
                                    madata.IsVirtualkey = false;
                                    madata.IsLabour = false;
                                    madata.IsNumber = true;
                                    madata.IsKeyprice = false;
                                    madata.IsCruxMaterial = false;
                                    madata.IsQuantity = false;
                                    madata.IsExtensiveProduction = false;
                                    madata.IsBecode = true;
                                    madata.PackageNum = 1;
                                    madata.IsSingle = false;
                                    madata.IsStandard = false;
                                    madata.IsSpec = false;
                                    madata.CreateTime = DateTime.Now;
                                    madata.CreatorId = _operator.UserId;
                                    madata.Deleted = false;
                                    madata.UsageStatus = true;
                                    MaterialList.Add(madata);
                                    DesignDetail.MaterialId = maid;
                                }
                            }
                            else
                            {
                                //通过成品编号绑定物料主键ID
                                if (dicMaterialSpace.Any(s => s.ProductCode == item.ProductCode.Trim()))
                                {
                                    //当前成品编码存在
                                    DesignDetail.MaterialId = dicMaterialSpace.FirstOrDefault(x => x.ProductCode == item.ProductCode.Trim()).Id;
                                }
                                else
                                {
                                    //当前成品编码不存在
                                    SpaceString += "[编号：" + item.ProductCode.Trim() + "],";
                                }
                            }
                            DataInfo.Add(DesignDetail);
                        }
                        else if (item.TorF == false)
                        {
                            PB_DesignDetail DesignDetail = new PB_DesignDetail();
                            //通过成品编号绑定物料主键ID
                            if (dicMaterialSpace.Any(s => s.ProductCode == item.ProductCode.Trim()))
                            {
                                //当前成品编码存在
                                DesignDetail.MaterialId = dicMaterialSpace.FirstOrDefault(x => x.ProductCode == item.ProductCode.Trim()).Id;
                            }
                            else
                            {
                                //当前成品编码不存在
                                SpaceString += "[编号：" + item.ProductCode.Trim() + "],";
                            }
                            DesignDetail.Id = IdHelper.GetId();
                            DesignDetail.ParentId = data.Id;
                            DesignDetail.Nums = Convert.ToDouble(item.Nums);
                            DesignDetail.RealNums = Convert.ToDouble(item.RealNums);
                            DesignDetail.ShipmentNums = 0;
                            DesignDetail.CreatorId = _operator?.UserId;
                            DesignDetail.CreateTime = DateTime.Now;
                            DesignDetail.State = 2;
                            DesignDetail.VersionNumber = 0;
                            DesignDetail.ShipmentNums = 0;
                            DesignDetail.Slot = item.Slot;
                            DesignDetail.Remarks = item.Remarks;
                            DataInfo.Add(DesignDetail);
                        }
                    }
                    //成品编码不存在则return
                    if (!string.IsNullOrEmpty(SpaceString))
                    {
                        return Error(ReturnValue = "成品编码不存在！" + SpaceString.Substring(0, SpaceString.Length - 1));
                    }

                    List<PB_DesignDetail> DesignDetailinfo = new List<PB_DesignDetail>();
                    //获取变更版本
                    PB_DesignListVersion Versiondata = _pB_DesignListBus.GetDesignListVersionAsync(data.Id);
                    //int inti = Importlist.Count;

                    foreach (var item in DataInfo)
                    {
                        PB_DesignDetail pbdata = new PB_DesignDetail();
                        pbdata.Id = item.Id;
                        pbdata.ParentId = item.ParentId;
                        pbdata.MaterialId = item.MaterialId;
                        pbdata.Nums = item.Nums;
                        pbdata.RealNums = item.RealNums;
                        pbdata.ShipmentNums = item.ShipmentNums;
                        pbdata.Remarks = item.Remarks;
                        pbdata.CreateTime = item.CreateTime;
                        pbdata.CreatorId = item.CreatorId;
                        pbdata.Deleted = item.Deleted;
                        pbdata.Weight = item.Weight;
                        pbdata.VersionNumber = Versiondata.VersionNumber;
                        pbdata.State = item.State;
                        pbdata.ReplaceId = pbdata.ReplaceId;
                        pbdata.Slot = item.Slot;
                        if (pbdata.State == 1)
                        {
                            pbdata.OneStep = pbdata.RealNums;
                        }
                        if (pbdata.State == 2)
                        {

                            double? jianshaonuns = 0;

                            var dbmater = _pB_DesignDetailBus.GetQueryable<PB_DesignDetail>().Where(m => m.ParentId == item.ParentId && m.MaterialId == item.MaterialId).FirstOrDefault();
                            if (dbmater == null)
                            {
                                var mater = _pB_DesignDetailBus.GetQueryable<PB_Material>().Where(s => s.Id == item.MaterialId).FirstOrDefault();
                                return Error(ReturnValue = $"{mater.Name}不存在设计清单当中请先进行补加!");
                            }
                            //获取第十五步补加的信息
                            var pB_DesignDetail2 = DetailList.Where(m => m.State < 2 && m.MaterialId == pbdata.MaterialId).ToList();
                            var Wuliao = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FifteenStep = x.Sum(p => p.FifteenStep) }).FirstOrDefault();
                            //获取第十五步变更减少sum的总数
                            var pB_DesignDetailDelete = DetailList.Where(m => m.State == 2 && m.MaterialId == pbdata.MaterialId).ToList();
                            var Wuliao2 = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FifteenStep = x.Sum(p => p.FifteenStep) }).FirstOrDefault();



                            //获取第十五步数量效验
                            var FifteenStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FifteenStep = x.Sum(p => p.FifteenStep) }).FirstOrDefault();
                            var FifteenStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FifteenStepReady = x.Sum(p => p.FifteenStepReady) }).FirstOrDefault();
                            var FifteenStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FifteenStep = x.Sum(p => p.FifteenStep) }).FirstOrDefault();
                            var FifteenStepDeleteReady = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FifteenStepReady = x.Sum(p => p.FifteenStepReady) }).FirstOrDefault();
                            double? numsFifteenStep = (FifteenStepNums.FifteenStep - FifteenStepReady.FifteenStepReady) - ((FifteenStepDeleteNums == null ? 0 : FifteenStepDeleteNums.FifteenStep) - (FifteenStepDeleteReady == null ? 0 : FifteenStepDeleteReady.FifteenStepReady));
                            jianshaonuns += (Wuliao == null ? 0 : Wuliao.FifteenStep) - (Wuliao2 == null ? 0 : Wuliao2.FifteenStep);
                            jianshaonuns += (FifteenStepNums == null ? 0 : FifteenStepNums.FifteenStep) - (FifteenStepDeleteNums == null ? 0 : FifteenStepDeleteNums.FifteenStep);


                            //获取第十步数量效验
                            var TenStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, TenStep = x.Sum(p => p.TenStep) }).FirstOrDefault();
                            var TenStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, TenStepReady = x.Sum(p => p.TenStepReady) }).FirstOrDefault();
                            var TenStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, TenStep = x.Sum(p => p.TenStep) }).FirstOrDefault();
                            var TenStepDeleteReady = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, TenStepReady = x.Sum(p => p.TenStepReady) }).FirstOrDefault();
                            double? numsTenStep = ((TenStepNums == null ? 0 : TenStepNums.TenStep) - (TenStepReady == null ? 0 : TenStepReady.TenStepReady)) - ((TenStepDeleteNums == null ? 0 : TenStepDeleteNums.TenStep) - (TenStepDeleteReady == null ? 0 : TenStepDeleteReady.TenStepReady));
                            ;


                            //获取第九步数量效验 
                            var NineStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, NineStep = x.Sum(p => p.NineStep) }).FirstOrDefault();
                            var NineStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, NineStepReady = x.Sum(p => p.NineStepReady) }).FirstOrDefault();
                            var NineStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, NineStep = x.Sum(p => p.NineStep) }).FirstOrDefault();
                            var NineStepReadyDelete = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, NineStepReady = x.Sum(p => p.NineStepReady) }).FirstOrDefault();
                            double? numsNineStep = ((NineStepNums == null ? 0 : NineStepNums.NineStep) - (NineStepReady == null ? 0 : NineStepReady.NineStepReady)) - ((NineStepDeleteNums == null ? 0 : NineStepDeleteNums.NineStep) - (NineStepReadyDelete == null ? 0 : NineStepReadyDelete.NineStepReady));


                            //获取第八步数量效验
                            var EightStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, EightStep = x.Sum(p => p.EightStep) }).FirstOrDefault();
                            var EightStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, EightStepReady = x.Sum(p => p.EightStepReady) }).FirstOrDefault();
                            var EightStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, EightStep = x.Sum(p => p.EightStep) }).FirstOrDefault();
                            var EightStepReadyDelete = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, EightStepReady = x.Sum(p => p.EightStepReady) }).FirstOrDefault();
                            double? numsEightStep = ((EightStepNums == null ? 0 : EightStepNums.EightStep) - (EightStepReady == null ? 0 : EightStepReady.EightStepReady)) - ((EightStepDeleteNums == null ? 0 : EightStepDeleteNums.EightStep) - (EightStepReadyDelete == null ? 0 : EightStepReadyDelete.EightStepReady));


                            //获取七步数量效验
                            var SevenStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, SevenStep = x.Sum(p => p.SevenStep) }).FirstOrDefault();
                            var SevenStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, SevenStepReady = x.Sum(p => p.SevenStepReady) }).FirstOrDefault();
                            var SevenStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, SevenStep = x.Sum(p => p.SevenStep) }).FirstOrDefault();
                            var SevenStepReadyDelete = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, SevenStepReady = x.Sum(p => p.SevenStepReady) }).FirstOrDefault();
                            double? numsSevenStep = ((SevenStepNums == null ? 0 : SevenStepNums.SevenStep) - (SevenStepReady == null ? 0 : SevenStepReady.SevenStepReady)) - ((SevenStepDeleteNums == null ? 0 : SevenStepDeleteNums.SevenStep) - (SevenStepReadyDelete == null ? 0 : SevenStepReadyDelete.SevenStepReady));


                            //获取第六步数量效验
                            var SixStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, SixStep = x.Sum(p => p.SixStep) }).FirstOrDefault();
                            var SixStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, SixStepReady = x.Sum(p => p.SixStepReady) }).FirstOrDefault();
                            var SixStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, SixStep = x.Sum(p => p.SixStep) }).FirstOrDefault();
                            var SixStepReadyDelete = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, SixStepReady = x.Sum(p => p.SixStepReady) }).FirstOrDefault();
                            double? numsSixStep = ((SevenStepNums == null ? 0 : SixStepNums.SixStep) - (SixStepReady == null ? 0 : SixStepReady.SixStepReady)) - ((SevenStepDeleteNums == null ? 0 : SixStepDeleteNums.SixStep) - (SixStepReadyDelete == null ? 0 : SixStepReadyDelete.SixStepReady));


                            //获取第五步数量效验
                            var FiveStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FiveStep = x.Sum(p => p.FiveStep) }).FirstOrDefault();
                            var FiveStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FiveStepReady = x.Sum(p => p.FiveStepReady) }).FirstOrDefault();
                            var FiveStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FiveStep = x.Sum(p => p.FiveStep) }).FirstOrDefault();
                            var FiveStepReadyDelete = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FiveStepReady = x.Sum(p => p.FiveStepReady) }).FirstOrDefault();
                            double? numsFiveStep = ((FiveStepNums == null ? 0 : FiveStepNums.FiveStep) - (FiveStepReady == null ? 0 : FiveStepReady.FiveStepReady)) - ((FiveStepDeleteNums == null ? 0 : FiveStepDeleteNums.FiveStep) - (FiveStepReadyDelete == null ? 0 : FiveStepReadyDelete.FiveStepReady));

                            //获取第四步数量效验
                            var FourStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FourStep = x.Sum(p => p.FourStep) }).FirstOrDefault();
                            var FourStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FourStepReady = x.Sum(p => p.FourStepReady) }).FirstOrDefault();
                            var FourStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FourStep = x.Sum(p => p.FourStep) }).FirstOrDefault();
                            var FourStepReadyDelete = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, FourStepReady = x.Sum(p => p.FourStepReady) }).FirstOrDefault();
                            double? numsFourStep = ((FourStepNums == null ? 0 : FourStepNums.FourStep) - (FourStepReady == null ? 0 : FourStepReady.FourStepReady)) - ((FiveStepDeleteNums == null ? 0 : FourStepDeleteNums.FourStep) - (FourStepReadyDelete == null ? 0 : FourStepReadyDelete.FourStepReady));


                            //获取第三步数量效验
                            var ThreeStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, ThreeStep = x.Sum(p => p.ThreeStep) }).FirstOrDefault();
                            var ThreeStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, ThreeStepReady = x.Sum(p => p.ThreeStepReady) }).FirstOrDefault();
                            var ThreeStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, ThreeStep = x.Sum(p => p.ThreeStep) }).FirstOrDefault();
                            var ThreeStepReadyDelete = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, ThreeStepReady = x.Sum(p => p.ThreeStepReady) }).FirstOrDefault();
                            double? numsThreeStep = ((FourStepNums == null ? 0 : ThreeStepNums.ThreeStep) - (ThreeStepReady == null ? 0 : ThreeStepReady.ThreeStepReady)) - ((ThreeStepDeleteNums == null ? 0 : ThreeStepDeleteNums.ThreeStep) - (ThreeStepReadyDelete == null ? 0 : ThreeStepReadyDelete.ThreeStepReady));

                            //获取第二步数量效验
                            var TwoStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, TwoStep = x.Sum(p => p.TwoStep) }).FirstOrDefault();
                            var TwoStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, TwoStepReady = x.Sum(p => p.TwoStepReady) }).FirstOrDefault();
                            var TwoStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, TwoStep = x.Sum(p => p.TwoStep) }).FirstOrDefault();
                            var TwoStepReadyDelete = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, TwoStepReady = x.Sum(p => p.TwoStepReady) }).FirstOrDefault();
                            double? numsTwoStep = ((TwoStepNums == null ? 0 : TwoStepNums.TwoStep) - (TwoStepReady == null ? 0 : TwoStepReady.TwoStepReady)) - ((TwoStepDeleteNums == null ? 0 : TwoStepDeleteNums.TwoStep) - (TwoStepReadyDelete == null ? 0 : TwoStepReadyDelete.TwoStepReady));

                            //获取第一步数量效验
                            var OneStepNums = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, OneStep = x.Sum(p => p.OneStep) }).FirstOrDefault();
                            var OneStepReady = pB_DesignDetail2.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, OneStepReady = x.Sum(p => p.OneStepReady) }).FirstOrDefault();
                            var OneStepDeleteNums = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, OneStep = x.Sum(p => p.OneStep) }).FirstOrDefault();
                            var OneStepReadyDelete = pB_DesignDetailDelete.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, OneStepReady = x.Sum(p => p.OneStepReady) }).FirstOrDefault();
                            double? numsOneStep = ((OneStepNums == null ? 0 : OneStepNums.OneStep) - (OneStepReady == null ? 0 : OneStepReady.OneStepReady)) - ((OneStepDeleteNums == null ? 0 : OneStepDeleteNums.OneStep) - (OneStepReadyDelete == null ? 0 : OneStepReadyDelete.OneStepReady));


                            double? CalculateSum = numsTenStep + numsNineStep + numsEightStep + numsSevenStep + numsSixStep + numsFiveStep + numsFourStep + numsThreeStep + numsTwoStep + numsOneStep;
                            if (CalculateSum < pbdata.RealNums)
                            {
                                double? jian = jianshaonuns + CalculateSum;
                                double? nums = (pbdata.RealNums - CalculateSum);
                                var jiannums = numsFifteenStep + CalculateSum;

                                if (jian < pbdata.RealNums)
                                {
                                    var mater = _pB_DesignDetailBus.GetQueryable<PB_Material>().Where(s => s.Id == item.MaterialId).FirstOrDefault();
                                    return Error(ReturnValue = $"{mater.Name}减少数量超出或已备货");
                                }
                                else
                                {
                                    pbdata.FifteenStep = nums;
                                }


                            }
                            double? Value = numsTenStep;
                            double? surplus = 0;
                            surplus = Value - pbdata.RealNums;
                            if (surplus < 0)
                            {

                                if (numsTenStep > 0 && surplus < 0)
                                {
                                    surplus = numsTenStep >= -surplus ? -surplus : surplus + numsTenStep;
                                    pbdata.TenStep = surplus >= 0 ? surplus : numsTenStep;

                                }

                                if (numsNineStep > 0 && surplus < 0)
                                {

                                    surplus = numsNineStep >= -surplus ? -surplus : surplus + numsNineStep;
                                    pbdata.NineStep = surplus >= 0 ? surplus : numsNineStep;

                                }

                                if (numsEightStep > 0 && surplus < 0)
                                {

                                    surplus = numsEightStep >= -surplus ? -surplus : surplus + numsEightStep;
                                    pbdata.EightStep = surplus >= 0 ? surplus : numsEightStep;

                                }
                                if (numsSevenStep > 0 && surplus < 0)
                                {

                                    surplus = numsSevenStep >= -surplus ? -surplus : surplus + numsSevenStep;
                                    pbdata.SevenStep = surplus >= 0 ? surplus : numsSevenStep;

                                }
                                if (numsSixStep > 0 && surplus < 0)
                                {

                                    surplus = numsSixStep >= -surplus ? -surplus : surplus + numsSixStep;
                                    pbdata.SixStep = surplus >= 0 ? surplus : numsSixStep;
                                }
                                if (numsFiveStep > 0 && surplus < 0)
                                {

                                    surplus = numsFiveStep >= -surplus ? -surplus : surplus + numsFiveStep;
                                    pbdata.FiveStep = surplus >= 0 ? surplus : numsFiveStep;
                                }
                                if (numsFourStep > 0 && surplus < 0)
                                {
                                    surplus = numsFourStep >= -surplus ? -surplus : surplus + numsFourStep;
                                    pbdata.FourStep = surplus >= 0 ? surplus : numsFourStep;
                                }
                                if (numsThreeStep > 0 && surplus < 0)
                                {
                                    surplus = numsThreeStep >= -surplus ? -surplus : surplus + numsThreeStep;
                                    pbdata.ThreeStep = surplus >= 0 ? surplus : numsThreeStep;
                                }
                                if (numsTwoStep > 0 && surplus < 0)
                                {
                                    surplus = numsTwoStep >= -surplus ? -surplus : surplus + numsTwoStep;
                                    pbdata.TwoStep = surplus >= 0 ? surplus : numsTwoStep;
                                }
                                if (numsOneStep > 0 && surplus < 0)
                                {
                                    surplus = numsOneStep >= -surplus ? -surplus : surplus + numsOneStep;
                                    pbdata.OneStep = surplus >= 0 ? surplus : numsOneStep;
                                }

                            }
                        }
                        DesignDetailinfo.Add(pbdata);
                    }
                    // await _pB_DesignListBus.PushChangMessage(data.Id);
                    await _pB_DesignDetailBus.AddDataExlAsync(DesignDetailinfo, data.Id);
                    await _pB_MaterialBus.AddDataExlAsync(MaterialList, _redisOptions, _mongodbOptions);
                    //await _pB_DesignListBus.UpdateData(UpdatepB_Designs);
                    await _pB_DesignListBus.UpdateDataAsync(data);
                    await _pB_DesignListBus.updatastate(data.Id);
                    if (StandardParts.Count > 0)
                    {
                        string str = string.Join(",", StandardParts);
                        ReturnValue = $"数据导入成功,共导入{DataInfo.Count}条数据,本次包含非标件。";
                    }
                    else
                    {
                        ReturnValue = $"数据导入成功,共导入{DataInfo.Count}条数据。";
                    }
                }
                else if (!flag)
                {
                    return Error(ReturnValue = "数据存在问题！" + ReturnValue);
                }
            }
            catch (Exception ex)
            {
                return Error("文件导入失败请检查文件内容");
            }

            return Success(ReturnValue);
        }
        /// <summary>
        /// 补加单导入   
        /// </summary>
        /// <param name="file"></param>
        /// <param name="ProjectId"></param>
        /// <param name="BuildingNumberID"></param>
        /// <returns></returns>
        public async Task<AjaxResult> ImportOrder(IFormFile file, string ProjectId, string BuildingNumberID)
        {
            List<string> StandardParts = new List<string>();
            string ReturnValue = string.Empty;
            //定义一个bool类型的变量用来做验证
            bool flag = true;
            try
            {
                //通过项目楼号判断清单是否存在
                var data = _pB_DesignListBus.GetQueryable<PB_DesignList>().Where(m => m.ProjectId == ProjectId && m.BuildingNumberID == BuildingNumberID && m.Type == 1).FirstOrDefault();
                if (data == null)
                {
                    return Error(ReturnValue = "清单不存在！");
                }
                if (data.ExamineStatus < 2 || data.ExamineStatus == 4)
                {
                    return Error(ReturnValue = "设计清单当前状态不允许补加导入！");
                }
                //查询设计清单明细排序最大的值
                //var datadetail = _pB_DesignDetailBus.GetQueryable<PB_DesignDetail>().Where(x => x.ParentId == data.Id).OrderByDescending(x => x.Slot).FirstOrDefault();
                //获取现有设计清单明细
                //List<PB_DesignDetail> DesignDetailList = _pB_DesignListBus.GetQueryable<PB_DesignDetail>().Where(m => m.ParentId == data.Id).ToList();
                #region 检查文件
                string fileExt = Path.GetExtension(file.FileName).ToLower();
                //定义一个集合一会儿将数据存储进来,全部一次丢到数据库中保存
                var Data = new List<PB_DesignList>();
                var DataInfo = new List<PB_DesignDetail>();
                List<DesignImportDTO> Importlist = new List<DesignImportDTO>();
                List<PB_Material> MaterialList = new List<PB_Material>();
                List<Base_UnitItems> dicMaterialUnit = new List<Base_UnitItems>();
                var liststring = new List<string>();
                ///如果物料已存在 则更改 他的数量
                List<PB_DesignDetail> UpdatepB_Designs = new List<PB_DesignDetail>();
                MemoryStream ms = new MemoryStream();
                file.CopyTo(ms);
                ms.Seek(0, SeekOrigin.Begin);
                IWorkbook book;
                if (fileExt == ".xlsx")
                {
                    book = new XSSFWorkbook(ms);
                }
                else if (fileExt == ".xls")
                {
                    book = new HSSFWorkbook(ms);
                }
                else
                {
                    book = null;
                }
                ISheet sheet = book.GetSheetAt(0);
                int CountRow = sheet.LastRowNum + 1;//获取总行数
                if (CountRow - 1 == 0)
                {
                    return Error("Excel列表数据项为空!");
                }
                #endregion
                #region 循环验证
                flag = true;
                #endregion
                List<string> listMaterialUnit = new List<string>();
                if (flag)
                {
                    //int index = Convert.ToInt32(datadetail.Slot);
                    for (int i = 0; i < CountRow; i++)//
                    {
                        var row = sheet.GetRow(i);
                        if (i < 7)
                        {
                            if (i == 0)
                            {
                                //绑定项目名称
                               
                                    string ExcelKey = row.GetCell(0).ToString();
                                    if (ExcelKey != "QspZCjj7iEGrPWQUg4eqyQ")
                                    {
                                        return Error("导入为非法模板,请下载系统标准模板整理数据导入!");
                                    }
                               
                            }
                            if (i == 1)
                            {
                                //绑定项目名称
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    //data.Name = row.GetCell(0).ToString();
                                }
                            }
                            if (i == 2)
                            {
                                //绑定工程名称/ID/创建者ID
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    //data.ProjectName = row.GetCell(0).ToString().Split("：")?[1];
                                }
                                //绑定机位数量
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    data.StandsNums = row.GetCell(6).ToString().Split("：")?[1];
                                }
                            }
                            if (i == 3)
                            {

                                //绑定工程地址
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    data.ProjectAddress = row.GetCell(0).ToString().Split("：")?[1];
                                }
                                //架体颜色
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    data.Color = row.GetCell(6).ToString().Split("：")[1];
                                }
                            }
                            if (i == 4)
                            {
                                //绑定业务负责人
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    data.Salesman = row.GetCell(0).ToString().Split("：")[1];
                                }
                                //绑定业务负责人电话号码
                                if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                                {
                                    data.Phone0 = row.GetCell(2).ToString().Split("：")[1];
                                }
                                //进场时间
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    string tmpDate = row.GetCell(6).ToString().Split('：')[1];
                                    if (!tmpDate.IsNullOrEmpty())
                                    {
                                        if (tmpDate.IndexOf("/") > -1)
                                        {
                                            tmpDate = tmpDate.Replace("/", "-");
                                        }
                                        data.InDate = Convert.ToDateTime(tmpDate);
                                    }
                                    else
                                    {
                                        data.InDate = DateTime.Now;
                                    }
                                }
                            }
                            if (i == 5)
                            {
                                //绑定接货负责人
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    data.Receiver = row.GetCell(0).ToString().Split("：")[1];
                                }
                                //绑定接货负责人联系电话
                                if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                                {
                                    data.Phone1 = row.GetCell(2).ToString().Split("：")[1];
                                }
                                //下单时间
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    var dateInfo = row.GetCell(6).ToString().Split('：')[1];
                                    if (!dateInfo.IsNullOrEmpty())
                                    {
                                        if (dateInfo.IndexOf("/") > -1)
                                        {
                                            dateInfo = dateInfo.Replace("/", "-");
                                        }
                                        data.OrderDate = Convert.ToDateTime(dateInfo);
                                    }
                                    else
                                    {
                                        data.OrderDate = DateTime.Now;
                                    }
                                }
                            }
                            if (i == 6)
                            {
                                //补加说明
                                if (row?.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    data.Remarks += row.GetCell(0).ToString().Split('：')[1];
                                }
                            }
                        }
                        if (i >= 8)
                        {
                            if (row != null)
                            {
                                //PB_DesignDetail pB_DesignDetail = new PB_DesignDetail();
                                DesignImportDTO desdata = new DesignImportDTO();
                                if (!row.GetCell(0).IsNullOrEmpty())
                                {

                                    //如果成品编号不存在则跳过
                                    if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                                    {
                                        //index++;
                                        //获取当前行号
                                        //pB_DesignDetail.Slot = index;
                                        desdata.Slot = IdHelper.GetId();
                                        if (row.GetCell(1) != null && row.GetCell(1).ToString().Trim().Length > 0)
                                        {
                                            desdata.Name = row.GetCell(1).ToString();
                                        }
                                        if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                                        {
                                            desdata.ProductCode = row.GetCell(2).ToString();
                                        }
                                        //绑定理论重量
                                        if (row.GetCell(3) != null && row.GetCell(3).ToString().Trim().Length > 0)
                                        {
                                            //pB_DesignDetail.Weight = Convert.ToDouble(row.GetCell(3).ToString());
                                            if (row.GetCell(3).CellType == CellType.Formula)
                                            {
                                                row.GetCell(3).SetCellType(CellType.String);
                                                desdata.Weight = Convert.ToDouble(row.GetCell(3).StringCellValue);
                                            }
                                            else
                                            {
                                                desdata.Weight = PB_DesignListController.IsNumber(row.GetCell(3).ToString()) == true ? Convert.ToDouble(row.GetCell(3).ToString()) : 0;
                                            }

                                        }
                                        if (row.GetCell(4) != null && row.GetCell(4).ToString().Trim().Length > 0)
                                        {
                                            desdata.unitName = row.GetCell(4).ToString();
                                        }
                                        if (row.GetCell(5) != null && row.GetCell(5).ToString().Trim().Length > 0)
                                        {
                                            desdata.Standard = row.GetCell(5).StringCellValue.ToString();
                                            if (row.GetCell(5).StringCellValue.ToString() == "是")
                                            {
                                                listMaterialUnit.Add(row.GetCell(4).ToString());
                                            }
                                        }

                                        //绑定设计数量
                                        if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                        {
                                            //desdata.Nums = row.GetCell(6).ToString();
                                            if (row.GetCell(6).CellType == CellType.Formula)
                                            {
                                                row.GetCell(6).SetCellType(CellType.String);
                                                desdata.Nums = Convert.ToDouble(row.GetCell(6).StringCellValue);
                                            }
                                            else
                                            {
                                                desdata.Nums = PB_DesignListController.IsNumber(row.GetCell(6).ToString()) == true ? Convert.ToDouble(row.GetCell(6).ToString()) : 0;
                                            }
                                        }
                                        //绑定实发数量
                                        if (row.GetCell(7) != null && row.GetCell(7).ToString().Trim().Length > 0)
                                        {
                                            if (row.GetCell(7).CellType == CellType.Formula)
                                            {
                                                row.GetCell(7).SetCellType(CellType.String);
                                                desdata.RealNums = Convert.ToDouble(row.GetCell(7).StringCellValue);
                                            }
                                            else
                                            {
                                                if (row.GetCell(7).ToString().Trim() == "相同")
                                                {
                                                    desdata.RealNums = desdata.Nums;
                                                }
                                                else
                                                {
                                                    if (row.GetCell(7).CellType == CellType.Formula)
                                                    {
                                                        row.GetCell(7).SetCellType(CellType.String);
                                                        desdata.RealNums = Convert.ToDouble(row.GetCell(7).StringCellValue);
                                                    }
                                                    else
                                                    {
                                                        desdata.RealNums = PB_DesignListController.IsNumber(row.GetCell(7).ToString()) == true ? Convert.ToDouble(row.GetCell(7).ToString()) : 0;
                                                    }
                                                }
                                            }
                                        }
                                        //绑定备注
                                        if (row.GetCell(8) != null && row.GetCell(8).ToString().Trim().Length > 0)
                                        {
                                            desdata.Remarks = row.GetCell(8).ToString();
                                            //pB_DesignDetail.Remarks += row.GetCell(8).ToString();
                                        }
                                        Importlist.Add(desdata);
                                        //DataInfo.Add(pB_DesignDetail);

                                    }
                                }
                            }
                        }
                    }
                    var listMaterialTypSpace = Importlist.Select(s => s.ProductCode).ToList();
                    //获取物料主键ID                    
                    var dicMaterialSpace = _pB_MaterialBus.GetQueryableByProductCode<PB_ProductCodeIdsDTO>(listMaterialTypSpace);

                    //物料分类
                    var materialTypedata = _pB_MaterialBus.GetDesignListMaterialTypeAsync();
                    string matercode = materialTypedata.MaterialCode;

                    if (listMaterialUnit.Count > 0)
                    {
                        //单位
                        dicMaterialUnit = _pB_MaterialBus.GetUnitItems<Base_UnitItems>(listMaterialUnit);
                    }

                    //获取变更版本
                    PB_DesignListVersion Versiondata = _pB_DesignListBus.GetDesignListVersionAsync(data.Id);
                    string SpaceString = "";
                    //插入子表
                    foreach (var item in Importlist)
                    {
                        PB_DesignDetail DesignDetail = new PB_DesignDetail();
                        DesignDetail.Id = IdHelper.GetId();
                        DesignDetail.ParentId = data.Id;
                        DesignDetail.Nums = Convert.ToDouble(item.Nums);
                        DesignDetail.RealNums = Convert.ToDouble(item.RealNums);
                        DesignDetail.ShipmentNums = 0;
                        DesignDetail.CreatorId = _operator?.UserId;
                        DesignDetail.CreateTime = DateTime.Now;
                        DesignDetail.State = 1;
                        DesignDetail.VersionNumber = Versiondata.VersionNumber;
                        DesignDetail.ShipmentNums = 0;
                        DesignDetail.FifteenStep = DesignDetail.RealNums;
                        DesignDetail.Slot = item.Slot;
                        DesignDetail.Remarks = item.Remarks;
                        if (item.Standard == "是")
                        {
                            //通过成品编号绑定物料主键ID
                            if (dicMaterialSpace.Any(s => s.ProductCode == item.ProductCode.Trim()))
                            {
                                StandardParts.Add(item.ProductCode);
                                //当前成品编码存在
                                DesignDetail.MaterialId = dicMaterialSpace.FirstOrDefault(x => x.ProductCode == item.ProductCode.Trim()).Id;
                            }
                            else
                            {
                                PB_Material madata = new PB_Material();
                                string maid = IdHelper.GetId();
                                madata.Id = maid;
                                madata.DeptId = _operator.Property?.FactoryId;
                                madata.Name = item.Name;
                                if (matercode.IsNullOrEmpty())
                                {
                                    madata.Code = "16.000001";
                                    matercode = "000001";
                                }
                                else
                                {
                                    if (matercode.Length == 6)
                                    {
                                        string code = "000000" + (Convert.ToInt32(matercode) + 1);
                                        madata.Code = "16." + code.Substring(code.Length - 6, 6);
                                        matercode = code.Substring(code.Length - 6, 6);
                                    }
                                    else
                                    {
                                        madata.Code = "16." + (Convert.ToInt32(matercode) + 1);
                                        matercode = (Convert.ToInt32(matercode) + 1).ToString();
                                    }
                                }
                                madata.ProductCode = item.ProductCode;
                                madata.Spec = item.ProductCode;
                                madata.SimpleName = item.Name;
                                madata.MaterialTypeId = materialTypedata.Id;
                                if (dicMaterialUnit.Any(s => s.Name == item.unitName.Trim()))
                                {
                                    //当前成品编码存在
                                    madata.UnitGroupId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).GroupId;
                                    madata.storageId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).Id;
                                    madata.saleId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).Id;
                                    madata.purchaseId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).Id;
                                    madata.productionId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).Id;
                                }
                                madata.Weight = Convert.ToDouble(item.Weight);
                                madata.IsProduct = true;
                                madata.IsMine = true;
                                madata.IsBuy = false;
                                madata.HasRef = false;
                                madata.IsVirtualkey = false;
                                madata.IsLabour = false;
                                madata.IsNumber = true;
                                madata.IsKeyprice = false;
                                madata.IsCruxMaterial = false;
                                madata.IsQuantity = false;
                                madata.IsExtensiveProduction = false;
                                madata.IsBecode = true;
                                madata.PackageNum = 1;
                                madata.IsSingle = false;
                                madata.IsStandard = false;
                                madata.IsSpec = false;
                                madata.CreateTime = DateTime.Now;
                                madata.CreatorId = _operator.UserId;
                                madata.Deleted = false;
                                madata.UsageStatus = true;
                                MaterialList.Add(madata);
                                DesignDetail.MaterialId = maid;
                            }
                        }
                        else
                        {
                            //通过成品编号绑定物料主键ID
                            if (dicMaterialSpace.Any(s => s.ProductCode == item.ProductCode.Trim()))
                            {
                                //当前成品编码存在
                                DesignDetail.MaterialId = dicMaterialSpace.FirstOrDefault(x => x.ProductCode == item.ProductCode.Trim()).Id;
                            }
                            else
                            {
                                //当前成品编码不存在
                                SpaceString += "[编号：" + item.ProductCode.Trim() + "],";
                            }
                        }
                        DataInfo.Add(DesignDetail);
                    }
                    //成品编码不存在则return
                    if (!string.IsNullOrEmpty(SpaceString))
                    {
                        return Error(ReturnValue = "成品编码不存在！" + SpaceString.Substring(0, SpaceString.Length - 1));
                    }
                    //List<PB_DesignDetail> DesignDetailinfo = new List<PB_DesignDetail>();

                    //int inti = Importlist.Count;

                    //foreach (var item in DataInfo)
                    //{
                    //    var itemdata = DesignDetailList.Where(x => x.MaterialId == item.MaterialId).FirstOrDefault();
                    //    if (itemdata == null)
                    //    {
                    //        inti++;
                    //        PB_DesignDetail pbdata = new PB_DesignDetail();
                    //        pbdata.Id = item.Id;
                    //        pbdata.ParentId = item.ParentId;
                    //        pbdata.MaterialId = item.MaterialId;
                    //        pbdata.Nums = item.Nums;
                    //        pbdata.RealNums = item.RealNums;
                    //        pbdata.ShipmentNums = item.ShipmentNums;
                    //        pbdata.Remarks = item.Remarks;
                    //        pbdata.CreateTime = item.CreateTime;
                    //        pbdata.CreatorId = item.CreatorId;
                    //        pbdata.Deleted = item.Deleted;
                    //        pbdata.Weight = item.Weight;
                    //        pbdata.VersionNumber = Versiondata.VersionNumber;
                    //        pbdata.State = item.State;
                    //        pbdata.ReplaceId = pbdata.ReplaceId;
                    //        pbdata.Slot = inti;
                    //        DesignDetailinfo.Add(pbdata);
                    //    }
                    //    else
                    //    {
                    //        itemdata.Nums += item.Nums;
                    //        itemdata.RealNums += item.RealNums;
                    //        itemdata.VersionNumber = Versiondata.VersionNumber;
                    //        UpdatepB_Designs.Add(itemdata);
                    //    }
                    //}

                    //导入之前确定推送钉钉推送
                    //此方法验证是否能够导入
                    //await _pB_DesignListBus.GetOverFlowMessage(data.Id);

                    await _pB_DesignDetailBus.AddDataExlAsync(DataInfo, data.Id);
                    await _pB_MaterialBus.AddDataExlAsync(MaterialList, _redisOptions, _mongodbOptions);
                    await _pB_DesignListBus.UpdateData(UpdatepB_Designs);
                    await _pB_DesignListBus.UpdateDataAsync(data);
                    await _pB_DesignListBus.updatastate(data.Id);
                    if (StandardParts.Count > 0)
                    {
                        string str = string.Join(",", StandardParts);
                        ReturnValue = $"数据导入成功,共导入{DataInfo.Count}条数据,本次包含非标件。";
                    }
                    else
                    {
                        ReturnValue = $"数据导入成功,共导入{DataInfo.Count}条数据。";
                    }

                }
                else if (!flag)
                {
                    return Error(ReturnValue = "数据存在问题！" + ReturnValue);
                }
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }

            return Success(ReturnValue);

        }

        private bool IsInt(string intstring)
        {
            string pattern = @"^\d*$";
            return Regex.IsMatch(intstring, pattern);
        }

        /// <summary>
        /// 设计清单清单导入
        /// </summary>
        /// <param name="file"></param>
        /// <param name="ProjectId">项目主键</param>
        /// <param name="BuildingNumberID">楼号主键</param>
        /// <returns></returns>
        public async Task<AjaxResult> Import(IFormFile file, string ProjectId, string BuildingNumberID)// file
        {
            string cuo = string.Empty;
            List<String> xuliehao = new List<string>();
            List<string> StandardParts = new List<string>();
            string ReturnValue = string.Empty;
            string DesigId = "";
            double? valuekey = 7; 
            //定义一个bool类型的变量用来做验证
            bool flag = true;
            try
            {
                #region 检查文件
                string fileExt = Path.GetExtension(file.FileName).ToLower();
                //定义一个集合一会儿将数据存储进来,全部一次丢到数据库中保存
                List<PB_DesignList> Data = new List<PB_DesignList>();
                List<PB_DesignDetail> DataInfo = new List<PB_DesignDetail>();
                List<DesignImportDTO> Importlist = new List<DesignImportDTO>();
                List<PB_Material> MaterialList = new List<PB_Material>();
                List<Base_UnitItems> dicMaterialUnit = new List<Base_UnitItems>();
                List<string> liststring = new List<string>();
                MemoryStream ms = new MemoryStream();
                file.CopyTo(ms);
                ms.Seek(0, SeekOrigin.Begin);
                IWorkbook book;
                if (fileExt == ".xlsx")
                {
                    book = new XSSFWorkbook(ms);
                }
                else if (fileExt == ".xls")
                {
                    book = new HSSFWorkbook(ms);
                }
                else
                {
                    book = null;
                }
                ISheet sheet = book.GetSheetAt(0);

                int CountRow = sheet.LastRowNum + 1;//获取总行数
                if (CountRow - 1 == 0)
                {
                    return Error("Excel列表数据项为空!");

                }
                #endregion
                #region 循环验证
                flag = true;
                //for (int i = 1; i < CountRow; i++)
                //{
                //    //获取第i行的数据
                //    var row = sheet.GetRow(i);
                //    if (row != null)
                //    {
                //        for (int j = 0; j < row.LastCellNum; j++)
                //        {
                //            //第二行默认为空
                //            if (i != 1)
                //            {
                //                if (row.GetCell(j) == null || row.GetCell(j).ToString().Trim().Length == 0)
                //                {
                //                    flag = false;
                //                    //return Error(ReturnValue += $"第{i + 1}行,第{j + 1}列数据不能为空。");
                //                }
                //            }
                //        }
                //    }
                //}
                #endregion
                List<string> listMaterialUnit = new List<string>();
                List<string> ListMaterCount = new List<string>();
                if (flag)
                {
                    int index = 1;
                    PB_DesignList commodity = new PB_DesignList();
                    string KeyID = IdHelper.GetId();
                    for (int i = 0; i < CountRow; i++)
                    {
                        var row = sheet.GetRow(i);
                        //获取主表信息
                        if (i < 7)
                        {
                            if (i == 0)
                            {
                                //绑定项目名称
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    string ExcelKey = row.GetCell(0).ToString();
                                    if (ExcelKey != "QspZCjj7iEGrPWQUg4eqyQ")
                                    {
                                        return Error("导入为非法模板,请下载系统标准模板整理数据导入!");
                                    }
                                }
                            }
                            if (i == 1)
                            {
                                //绑定项目名称
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    commodity.Name = row.GetCell(0).ToString();
                                }
                            }
                            if (i == 2)
                            {
                                //绑定工程名称/ID/创建者ID
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    commodity.Id = KeyID;
                                    DesigId = KeyID;
                                    commodity.CreatorId = _operator?.UserId;
                                    commodity.StepLevel = 7;
                                    commodity.ProjectName = row.GetCell(0).ToString().Split("：")?[1];
                                }
                                //绑定机位数量
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    commodity.StandsNums = row.GetCell(6).ToString().Split("：")?[1];
                                }
                            }
                            if (i == 3)
                            {

                                //绑定工程地址
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    commodity.ProjectAddress = row.GetCell(0).ToString().Split("：")?[1];
                                }
                                //绑定提升方式
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    commodity.LiftingMode = row.GetCell(6).ToString().Split("：")[1];
                                }
                            }
                            if (i == 4)
                            {
                                //绑定业务负责人
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    commodity.Salesman = row.GetCell(0).ToString().Split("：")[1];
                                }
                                //绑定业务负责人电话号码
                                if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                                {
                                    commodity.Phone0 = row.GetCell(2).ToString().Split("：")[1];
                                }
                                //绑定架体步高
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    commodity.StepHeight = row.GetCell(6).ToString().Split("：")[1];
                                }
                            }
                            if (i == 5)
                            {
                                //绑定接货负责人
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    commodity.Receiver = row.GetCell(0).ToString().Split("：")[1];
                                }
                                //绑定接货负责人联系电话
                                if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                                {
                                    commodity.Phone1 = row.GetCell(2).ToString().Split("：")[1];
                                }
                                //绑定架体颜色
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    commodity.Color = row.GetCell(6).ToString().Split("：")[1];
                                }
                            }
                            if (i == 6)
                            {
                                //绑定进场时间
                                if (row?.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    string tmpDate = row.GetCell(0).ToString().Split('：')[1];
                                    if (!tmpDate.IsNullOrEmpty())
                                    {
                                        if (tmpDate.IndexOf("/") > -1)
                                        {
                                            tmpDate = tmpDate.Replace("/", "-");
                                        }
                                        commodity.InDate = Convert.ToDateTime(tmpDate);
                                    }
                                    else
                                    {
                                        commodity.InDate = DateTime.Now;
                                    }
                                }
                                //绑定下单时间
                                if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                                {
                                    var dateInfo = row.GetCell(2).ToString().Split('：')[1];
                                    if (!dateInfo.IsNullOrEmpty())
                                    {
                                        if (dateInfo.IndexOf("/") > -1)
                                        {
                                            dateInfo = dateInfo.Replace("/", "-");
                                        }
                                        commodity.OrderDate = Convert.ToDateTime(dateInfo);
                                    }
                                    else
                                    {
                                        commodity.OrderDate = DateTime.Now;
                                    }
                                }
                                //绑定网板样式
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    commodity.Style = row.GetCell(6).ToString().Split("：")[1];
                                }
                            }
                        }
                        else if (i == 7)
                        {
                            //补全其他主表信息
                            commodity.ProjectId = ProjectId;
                            commodity.BuildingNumberID = BuildingNumberID;
                            commodity.Deleted = false;
                            //commodity.InDate = DateTime.Now;
                            //commodity.OrderDate = DateTime.Now;
                            commodity.ExamineStatus = 1;
                            commodity.ApproverStatus = 0;
                            commodity.CreateTime = DateTime.Now;
                            commodity.Approver = commodity.Approver == null ? "" : commodity.Approver;
                            commodity.Calculation = commodity.Calculation == null ? "" : commodity.Calculation;
                            commodity.Code = commodity.Code == null ? "" : commodity.Code;
                            commodity.ContractNo = commodity.ContractNo == null ? "" : commodity.ContractNo;
                            commodity.DeptId = _operator?.Property.DepartmentId;
                            commodity.Examiner = commodity.Examiner == null ? "" : commodity.Examiner;
                            commodity.Name = commodity.Name == null ? "" : commodity.Name;
                            commodity.PicUrl = commodity.PicUrl == null ? "" : commodity.PicUrl;
                            commodity.ProjectId = commodity.ProjectId == null ? "" : commodity.ProjectId;
                            commodity.QrCode = "12," + KeyID;
                            commodity.Remarks = commodity.Remarks == null ? "" : commodity.Remarks;
                            commodity.IsPrint = false;
                            commodity.Type = 1;
                            Data.Add(commodity);
                        }

                        else if (i >= 8)
                        {
                           
                               //绑定明细表
                               //PB_DesignDetail pB_DesignDetail = new PB_DesignDetail();
                               DesignImportDTO desdata = new DesignImportDTO();
                            //如果第一列不为空（判断）
                            if (!row.GetCell(0).IsNullOrEmpty())
                            {
                                string xuhao = row.GetCell(0).ToString();

                                if (xuliehao.Exists(e => e == xuhao)) {
                                    return Error("请检查文件中的序列号!");
                                }
                                xuliehao.Add(xuhao);


                                //如果成品编号不存在则跳过
                                if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                                {
                                    //获取当前行号
                                    desdata.Slot = IdHelper.GetId();
                                    if (row.GetCell(1) != null && row.GetCell(1).ToString().Trim().Length > 0)
                                    {
                                        desdata.Name = row.GetCell(1).ToString();
                                        cuo = desdata.Name;
                                    }
                                    if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                                    {
                                        desdata.ProductCode = row.GetCell(2).ToString();
                                    }
                                    //绑定理论重量
                                    if (row.GetCell(3) != null && row.GetCell(3).ToString().Trim().Length > 0)
                                    {
                                        if (row.GetCell(3).CellType == CellType.Formula)
                                        {
                                            row.GetCell(3).SetCellType(CellType.String);
                                            desdata.Weight = Convert.ToDouble(row.GetCell(3).StringCellValue);
                                        }
                                        else
                                        {
                                            //desdata.Weight = PB_DesignListController.IsNumber(row.GetCell(3).ToString()) == true ? Convert.ToDouble(row.GetCell(3).ToString()) : 0;
                                            desdata.Weight = Convert.ToDouble(row.GetCell(3).ToString());
                                        }

                                    }
                                    if (row.GetCell(4) != null && row.GetCell(4).ToString().Trim().Length > 0)
                                    {
                                        desdata.unitName = row.GetCell(4).ToString();
                                    }
                                    if (row.GetCell(5) != null && row.GetCell(5).ToString().Trim().Length > 0)
                                    {
                                        desdata.Standard = row.GetCell(5).StringCellValue.ToString();
                                        if (row.GetCell(5).StringCellValue.ToString() == "是")
                                        {
                                            listMaterialUnit.Add(row.GetCell(4).ToString());
                                        }
                                    }

                                    //绑定设计数量
                                    if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                    {
                                        //desdata.Nums = row.GetCell(6).ToString();
                                        if (row.GetCell(6).CellType == CellType.Formula)
                                        {
                                            row.GetCell(6).SetCellType(CellType.String);
                                            desdata.Nums = Convert.ToDouble(row.GetCell(6).StringCellValue);
                                        }
                                        else
                                        {
                                            try
                                            {
                                                desdata.Nums = PB_DesignListController.IsNumber(row.GetCell(6).ToString()) == true ? Convert.ToDouble(row.GetCell(6).ToString()) : 0;
                                            }
                                            catch (Exception ex)
                                            {

                                                throw;
                                            }
                                                    
                                            
                                        }
                                    }
                                    //绑定实发数量
                                    if (row.GetCell(7) != null && row.GetCell(7).ToString().Trim().Length > 0)
                                    {
                                        if (row.GetCell(7).CellType == CellType.Formula)
                                        {
                                            row.GetCell(7).SetCellType(CellType.String);
                                            desdata.RealNums = Convert.ToDouble(row.GetCell(7).StringCellValue);
                                        }
                                        else
                                        {
                                            if (row.GetCell(7).ToString().Trim() == "相同")
                                            {
                                                desdata.RealNums = desdata.Nums;
                                            }
                                            else
                                            {
                                                if (row.GetCell(7).CellType == CellType.Formula)
                                                {
                                                    row.GetCell(7).SetCellType(CellType.String);
                                                    desdata.RealNums = Convert.ToDouble(row.GetCell(7).StringCellValue);
                                                }
                                                else
                                                {
                                                    desdata.RealNums = PB_DesignListController.IsNumber(row.GetCell(7).ToString()) == true ? Convert.ToDouble(row.GetCell(7).ToString()) : 0;
                                                }
                                            }
                                        }
                                    }
                                    //绑定备注
                                    if (row.GetCell(8) != null && row.GetCell(8).ToString().Trim().Length > 0)
                                    {
                                        desdata.Remarks = row.GetCell(8).ToString();
                                        //pB_DesignDetail.Remarks += row.GetCell(8).ToString();
                                    }
                                    //验证步级数量与设计数量对比
                                    double? QuantityRealNums = 0;
                                    if (row.GetCell(9) == null || row.GetCell(9).ToString() == "")
                                    {
                                        desdata.OneStep = 0;

                                    }
                                    else
                                    {
                                     

                                        Double nums = Convert.ToDouble(row.GetCell(9).ToString());
                                     bool  isnumber=IsInt(nums.ToString());
                                        if (isnumber == false)
                                        {
                                            return Error(ReturnValue = desdata.ProductCode + "不能输入小数");
                                        }
                                        else {
                                            desdata.OneStep = Convert.ToDouble(row.GetCell(9).ToString());
                                        }
                                       
                                    }
                                    if (desdata.OneStep < 0)
                                    {
                                        return Error(ReturnValue = "第一步设计数量不能小于0");
                                    }

                                    QuantityRealNums += desdata.OneStep;
                                    if (row.GetCell(10) == null || row.GetCell(10).ToString() == "")
                                    {
                                        desdata.TwoStep = 0;
                                    }
                                    else
                                    {
                                        bool isnumber = IsInt(row.GetCell(10).ToString());
                                        if (isnumber == false)
                                        {
                                            return Error(ReturnValue = desdata.ProductCode + "不能输入小数");
                                        }
                                        else
                                        {
                                            desdata.TwoStep = Convert.ToDouble(row.GetCell(10).ToString());
                                        }
                                        
                                    }
                                    if (desdata.TwoStep < 0)
                                    {
                                        return Error(ReturnValue = "第二步设计数量不能小于0");
                                    }
                                    QuantityRealNums += desdata.TwoStep;
                                    if (row.GetCell(11) == null || row.GetCell(11).ToString() == "")
                                    {
                                        desdata.ThreeStep = 0;
                                    }
                                    else
                                    {
                                        bool isnumber = IsInt(row.GetCell(11).ToString());
                                        if (isnumber == false)
                                        {
                                            return Error(ReturnValue = desdata.ProductCode + "不能输入小数");
                                        }
                                        else
                                        {
                                            desdata.ThreeStep = Convert.ToDouble(row.GetCell(11).ToString());
                                        }
                                       
                                    }
                                    if (desdata.ThreeStep < 0)
                                    {
                                        return Error(ReturnValue = "第三步设计数量不能小于0");
                                    }
                                    QuantityRealNums += desdata.ThreeStep;
                                    if (row.GetCell(12) == null || row.GetCell(12).ToString() == "")
                                    {
                                        desdata.FourStep = 0;
                                    }
                                    else
                                    {
                                        bool isnumber = IsInt(row.GetCell(12).ToString());
                                        if (isnumber == false)
                                        {
                                            return Error(ReturnValue = desdata.ProductCode + "不能输入小数");
                                        }
                                        else
                                        {
                                            desdata.FourStep = Convert.ToDouble(row.GetCell(12).ToString());
                                        }
                            
                                    }
                                    if (desdata.FourStep < 0)
                                    {
                                        return Error(ReturnValue = "第四步设计数量不能小于0");
                                    }
                                    QuantityRealNums += desdata.FourStep;
                                    if (row.GetCell(13) == null || row.GetCell(13).ToString() == "")
                                    {
                                        desdata.FiveStep = 0;
                                    }
                                    else
                                    {
                                        bool isnumber = IsInt(row.GetCell(13).ToString());
                                        if (isnumber == false)
                                        {
                                            return Error(ReturnValue = desdata.ProductCode + "不能输入小数");
                                        }
                                        else
                                        {
                                            desdata.FiveStep = Convert.ToDouble(row.GetCell(13).ToString());
                                        }
                                       
                                    }
                                    if (desdata.FiveStep < 0)
                                    {
                                        return Error(ReturnValue = "第五步设计数量不能小于0");
                                    }
                                    QuantityRealNums += desdata.FiveStep;
                                    if (row.GetCell(14) == null || row.GetCell(14).ToString() == "")
                                    {
                                        desdata.SixStep = 0;
                                    }
                                    else
                                    {
                                        bool isnumber = IsInt(row.GetCell(14).ToString());
                                        if (isnumber == false)
                                        {
                                            return Error(ReturnValue = desdata.ProductCode + "不能输入小数");
                                        }
                                        else
                                        {
                                            desdata.SixStep = Convert.ToDouble(row.GetCell(14).ToString());
                                        }
                                       
                                    }
                                    if (desdata.SixStep < 0)
                                    {
                                        return Error(ReturnValue = "第六步设计数量不能小于0");
                                    }
                                    QuantityRealNums += desdata.SixStep;
                                    if (row.GetCell(15) == null || row.GetCell(15).ToString() == "")
                                    {
                                        desdata.SevenStep = 0;
                                    }
                                    else
                                    {
                                        bool isnumber = IsInt(row.GetCell(15).ToString());
                                        if (isnumber == false)
                                        {
                                            return Error(ReturnValue = desdata.ProductCode + "不能输入小数");
                                        }
                                        else
                                        {
                                            desdata.SevenStep = Convert.ToDouble(row.GetCell(15).ToString());
                                        }
                                      
                                    }
                                    if (desdata.SevenStep < 0)
                                    {
                                        return Error(ReturnValue = "第七步设计数量不能小于0");
                                    }
                                    QuantityRealNums += desdata.SevenStep;
                                    if (row.GetCell(16) == null || row.GetCell(16).ToString() == "")
                                    {
                                        desdata.EightStep = 0;
                                    }
                                    else
                                    {
                                        bool isnumber = IsInt(row.GetCell(16).ToString());
                                        if (isnumber == false)
                                        {
                                            return Error(ReturnValue = desdata.ProductCode + "不能输入小数");
                                        }
                                        else
                                        {
                                            desdata.EightStep = Convert.ToDouble(row.GetCell(16).ToString());
                                        }
                                        
                                        if (valuekey < 9)
                                        {
                                            commodity.StepLevel = 9;
                                            valuekey = 9;
                                        }

                                    }
                                    if (desdata.EightStep < 0)
                                    {
                                        return Error(ReturnValue = "第八步设计数量不能小于0");
                                    }
                                    QuantityRealNums += desdata.EightStep;
                                    if (row.GetCell(17) == null || row.GetCell(17).ToString() == "")
                                    {
                                        desdata.NineStep = 0;
                                    }
                                    else
                                    {
                                        bool isnumber = IsInt(row.GetCell(17).ToString());
                                        if (isnumber == false)
                                        {
                                            return Error(ReturnValue = desdata.ProductCode + "不能输入小数");
                                        }
                                        else
                                        {
                                            desdata.NineStep = Convert.ToDouble(row.GetCell(17).ToString());
                                        }

                                       
                                        if (valuekey < 9)
                                        {
                                            commodity.StepLevel = 9;
                                            valuekey = 9;
                                        }
                                    }
                                    if (desdata.NineStep < 0)
                                    {
                                        return Error(ReturnValue = "第九步设计数量不能小于0");
                                    }
                                    QuantityRealNums += desdata.NineStep;
                                    if (row.GetCell(18) == null || row.GetCell(18).ToString() == "")
                                    {
                                        desdata.TenStep = 0;

                                    }
                                    else
                                    {
                                        bool isnumber = IsInt(row.GetCell(18).ToString());
                                        if (isnumber == false)
                                        {
                                            return Error(ReturnValue = desdata.ProductCode + "不能输入小数");
                                        }
                                        else
                                        {
                                            desdata.TenStep = Convert.ToDouble(row.GetCell(18).ToString());
                                        }
                                        
                                        if (valuekey < 10)
                                        {
                                            commodity.StepLevel = 10;
                                            valuekey = 10;
                                        }
                                    }
                                    if (desdata.TenStep < 0)
                                    {
                                        return Error(ReturnValue = "第十步设计数量不能小于0");
                                    }
                                    QuantityRealNums += desdata.TenStep;
                                    if (row.GetCell(19) == null || row.GetCell(19).ToString() == "")
                                    {
                                        desdata.ElevenStep = 0;
                                    }
                                    else
                                    {
                                        bool isnumber = IsInt(row.GetCell(19).ToString());
                                        if (isnumber == false)
                                        {
                                            return Error(ReturnValue = "不能输入小数");
                                        }
                                        else
                                        {
                                            desdata.ElevenStep = Convert.ToDouble(row.GetCell(19).ToString());
                                        }
                                       
                                        commodity.StepLevel = 10;
                                    }
                                    if (row.GetCell(20) == null || row.GetCell(20).ToString() == "")
                                    {
                                        desdata.TwelveStep = 0;
                                    }
                                    else
                                    {
                                        bool isnumber = IsInt(row.GetCell(20).ToString());
                                        if (isnumber == false)
                                        {
                                            return Error(ReturnValue = "不能输入小数");
                                        }
                                        else
                                        {
                                            desdata.TwelveStep = Convert.ToDouble(row.GetCell(20).ToString());
                                        }
                                       
                                    }
                                    if (row.GetCell(21) == null || row.GetCell(21).ToString() == "")
                                    {
                                        desdata.ThirteenStep = 0;
                                    }
                                    else
                                    {
                                        desdata.ThirteenStep = Convert.ToDouble(row.GetCell(21).ToString());
                                    }
                                    if (row.GetCell(22) == null || row.GetCell(22).ToString() == "")
                                    {
                                        desdata.FourteenStep = 0;
                                    }
                                    else
                                    {
                                        desdata.FourteenStep = Convert.ToDouble(row.GetCell(22).ToString());
                                    }
                                    if (row.GetCell(23) == null || row.GetCell(23).ToString() == "")
                                    {
                                        desdata.FifteenStep = 0;
                                    }
                                    else
                                    {
                                        desdata.FifteenStep = Convert.ToDouble(row.GetCell(23).ToString());
                                    }
                                    if (QuantityRealNums != desdata.RealNums)
                                    {
                                        var pb_marter = _pB_DesignListBus.GetQueryableOK<PB_Material>().Where(m => m.ProductCode == desdata.ProductCode).FirstOrDefault();
                                        if (pb_marter != null)
                                        {
                                            return Error(ReturnValue = $"{pb_marter.Name}步级设计数量与设计数量不匹配");
                                        }
                                        else
                                        {
                                            return Error(ReturnValue = $"{desdata.ProductCode}步级设计数量与设计数量不匹配");
                                        }
                                    }
                                    Importlist.Add(desdata);
                                    //DataInfo.Add(pB_DesignDetail);
                                    index++;
                                }
                            }
                             
                          
                           
                            else if (row.GetCell(0) != null&&row.GetCell(1).ToString().Contains("计算")&& row.GetCell(1).ToString().IsNullOrEmpty()) {
                                return Error("请检查文件中的序列号!");
                            }
                        }
                    }
                    //去重成品编码并赋给新集合（listMaterialTypSpace）
                    //var listMaterialTypSpace = DataInfo.Select(s => s.MaterialId).Select(s => s.Trim()).Distinct().ToList();
                    //var dicMaterialSpace = _pB_MaterialBus.GetQueryable<PB_Material>().Where(w => listMaterialTypSpace.Contains(w.ProductCode)).ToDictionary(k => k.Id, v => v.ProductCode);

                    var listMaterialTypSpace = Importlist.Select(s => s.ProductCode).ToList();
                    //获取物料主键ID                    
                    var dicMaterialSpace = _pB_MaterialBus.GetQueryableByProductCode<PB_ProductCodeIdsDTO>(listMaterialTypSpace);
                    var dicMaterialState = _pB_MaterialBus.GetQueryableByProductUsageStatusCode<PB_ProductCodeIdsDTO>(listMaterialTypSpace);
                    var materialTypedata = _pB_MaterialBus.GetDesignListMaterialTypeAsync();
                    string matercode = materialTypedata.MaterialCode;

                    if (listMaterialUnit.Count > 0)
                    {
                        dicMaterialUnit = _pB_MaterialBus.GetUnitItems<Base_UnitItems>(listMaterialUnit);
                    }

                    //物料类型
                    //var listMaterialTypeCodes = DataInfo.Select(s => s.MaterialId).Select(s => s.Trim()).Distinct().ToList();
                    //var dicMaterialType = _pB_MaterialBus.GetQueryable<PB_Material>().Where(w => listMaterialTypeCodes.Contains(w.Code)).ToDictionary(k => k.Id, v => v.Code);


                    //插入主表
                    //foreach (var item in Data)
                    //{
                    //    var listdata = _pB_MaterialBus.GetQueryable<PB_DesignList>().Where(w => w.ProjectName == item.ProjectName && w.Name == item.Name);
                    //    if (listdata.Count() > 0)
                    //    {
                    //        liststring.Add(item.ProjectName);
                    //    }
                    //}

                    var listdata = _pB_MaterialBus.GetQueryable<PB_DesignList>().Where(w => w.ProjectId == ProjectId && w.BuildingNumberID == BuildingNumberID && w.Type == 1).FirstOrDefault();
                    if (listdata != null)
                    {
                        liststring.Add(listdata.ProjectName);
                    }

                    string SpaceString = "";
                    string StateString = "";
                    //string NumberString = "";
                    //插入子表
                    foreach (var item in Importlist)
                    {
                        PB_DesignDetail DesignDetail = new PB_DesignDetail();
                        DesignDetail.Id = IdHelper.GetId();
                        DesignDetail.ParentId = KeyID;
                        DesignDetail.Nums = Convert.ToDouble(item.Nums);
                        DesignDetail.RealNums = Convert.ToDouble(item.RealNums);
                        DesignDetail.ShipmentNums = 0;
                        DesignDetail.CreatorId = _operator?.UserId;
                        DesignDetail.CreateTime = DateTime.Now;
                        DesignDetail.State = 0;
                        DesignDetail.VersionNumber = 1;
                        DesignDetail.ShipmentNums = 0;
                        //DesignDetail.Slot = item.Slot;
                        DesignDetail.Slot = IdHelper.GetId();
                        DesignDetail.Remarks = item.Remarks;
                        DesignDetail.Weight = item.Weight;
                        DesignDetail.OneStep = item.OneStep;
                        DesignDetail.TwoStep = item.TwoStep;
                        DesignDetail.ThreeStep = item.ThreeStep;
                        DesignDetail.FourStep = item.FourStep;
                        DesignDetail.FiveStep = item.FiveStep;
                        DesignDetail.SixStep = item.SixStep;
                        DesignDetail.SevenStep = item.SevenStep;
                        DesignDetail.EightStep = item.EightStep;
                        DesignDetail.NineStep = item.NineStep;
                        DesignDetail.TenStep = item.TenStep;
                        DesignDetail.ElevenStep = item.ElevenStep;
                        DesignDetail.TwelveStep = item.TwelveStep;
                        DesignDetail.ThirteenStep = item.ThirteenStep;
                        DesignDetail.FourteenStep = item.FourteenStep;
                        DesignDetail.FifteenStep = item.FifteenStep;
                        if (item.Standard == "是")
                        {
                            //通过成品编号绑定物料主键ID
                            if (dicMaterialSpace.Any(s => s.ProductCode == item.ProductCode.Trim()))
                            {

                                //插入非标件提示
                                StandardParts.Add(item.ProductCode);

                                //当前成品编码存在
                                DesignDetail.MaterialId = dicMaterialSpace.FirstOrDefault(x => x.ProductCode == item.ProductCode.Trim()).Id;
                                if (ListMaterCount.Contains(DesignDetail.MaterialId))
                                {
                                    var db = _pB_DesignListBus.GetQueryableOK<PB_Material>().Where(m => m.Id == DesignDetail.MaterialId).FirstOrDefault();
                                    return Error(ReturnValue = $"导入数据物料:{db.Name}重复");
                                }
                                ListMaterCount.Add(DesignDetail.MaterialId);
                            }
                            else
                            {
                                PB_Material madata = new PB_Material();
                                string maid = IdHelper.GetId();
                                madata.Id = maid;
                                madata.DeptId = _operator.Property?.FactoryId;
                                madata.Name = item.Name;
                                if (matercode.IsNullOrEmpty())
                                {
                                    madata.Code = "16.000001";
                                    matercode = "000001";
                                }
                                else
                                {
                                    if (matercode.Length == 6)
                                    {
                                        string code = "000000" + (Convert.ToInt32(matercode) + 1);
                                        madata.Code = "16." + code.Substring(code.Length - 6, 6);
                                        matercode = code.Substring(code.Length - 6, 6);
                                    }
                                    else
                                    {
                                        madata.Code = "16." + (Convert.ToInt32(matercode) + 1);
                                        matercode = (Convert.ToInt32(matercode) + 1).ToString();
                                    }
                                }
                              var madataProductCode =await _pB_DesignListBus.GetProductCodeData(item.ProductCode);
                                if (madataProductCode!=null) {
                                    return Error(ReturnValue = "成品编码已存在:" + item.ProductCode);
                                }
                                madata.ProductCode = item.ProductCode;
                                
                                madata.Spec = item.ProductCode;
                                madata.SimpleName = item.Name;
                                madata.MaterialTypeId = materialTypedata.Id;
                                if (dicMaterialUnit.Any(s => s.Name == item.unitName.Trim()))
                                {
                                    //当前成品编码存在
                                    madata.UnitGroupId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).GroupId;
                                    madata.storageId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).Id;
                                    madata.saleId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).Id;
                                    madata.purchaseId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).Id;
                                    madata.productionId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).Id;
                                }
                                madata.Weight = Convert.ToDouble(item.Weight);
                                madata.IsProduct = true;
                                madata.IsMine = true;
                                madata.IsBuy = false;
                                madata.HasRef = false;
                                madata.IsVirtualkey = false;
                                madata.IsLabour = false;
                                madata.IsNumber = true;
                                madata.IsKeyprice = false;
                                madata.IsCruxMaterial = false;
                                madata.IsQuantity = false;
                                madata.IsExtensiveProduction = false;
                                madata.IsBecode = true;
                                madata.PackageNum = 1;
                                madata.IsSingle = false;
                                madata.IsStandard = false;
                                madata.IsSpec = false;
                                madata.CreateTime = DateTime.Now;
                                madata.CreatorId = _operator.UserId;
                                madata.Deleted = false;
                                madata.UsageStatus = true;
                                MaterialList.Add(madata);
                                DesignDetail.MaterialId = maid;
                                
                                if (ListMaterCount.Contains(maid)) {
                                    var db = _pB_DesignListBus.GetQueryableOK<PB_Material>().Where(m=>m.Id==maid).FirstOrDefault();
                                    return Error(ReturnValue = $"导入数据物料:{db.Name}重复");
                                }
                                ListMaterCount.Add(maid);
                            }
                        }
                        else
                        {
                            //通过成品编号绑定物料主键ID
                            if (dicMaterialSpace.Any(s => s.ProductCode == item.ProductCode.Trim()))
                            {
                                //当前成品编码存在
                                DesignDetail.MaterialId = dicMaterialSpace.FirstOrDefault(x => x.ProductCode == item.ProductCode.Trim()).Id;
                               
                                if (ListMaterCount.Contains(DesignDetail.MaterialId))
                                {
                                    var db = _pB_DesignListBus.GetQueryableOK<PB_Material>().Where(m => m.Id == DesignDetail.MaterialId).FirstOrDefault();
                                    return Error(ReturnValue = $"导入数据物料:{db.Name}重复");
                                }
                                 ListMaterCount.Add(DesignDetail.MaterialId);
                            }
                            else
                            {
                                //当前成品编码不存在
                                SpaceString += "[编号：" + item.ProductCode.Trim() + "],";
                            }

                            if (dicMaterialState.Any(s => s.ProductCode == item.ProductCode.Trim()))
                            {
                                //当前成品编码存在
                                DesignDetail.MaterialId = dicMaterialState.FirstOrDefault(x => x.ProductCode == item.ProductCode.Trim()).Id;
                              
                            }
                            else
                            {
                                //当前成品编码不存在
                               StateString += "[编号：" + item.ProductCode.Trim() + "],";
                            }

                        }
                        DataInfo.Add(DesignDetail);
                    }
                    //成品编码不存在则return
                    if (!string.IsNullOrEmpty(SpaceString))
                    {
                        //string aaa = "物料编码不存在！" + SpaceString.Substring(0, SpaceString.Length - 1);
                        return Error(ReturnValue = "成品编码不存在:" + SpaceString.Substring(0, SpaceString.Length - 1));
                    }
                    if (!string.IsNullOrEmpty(StateString))
                    {
                        //string aaa = "物料编码不存在！" + SpaceString.Substring(0, SpaceString.Length - 1);
                        return Error(ReturnValue = "物料已停用:" + StateString.Substring(0, StateString.Length - 1));
                    }
                    //主表存在则返回
                    if (liststring.Count() > 0)
                    {
                        return Error(ReturnValue = "导入的数据中有已存在数据库中,请不要重复导入");
                    }
                    if (DataInfo.Count > 0)
                    {
                        //插入主子表
                        await _pB_DesignDetailBus.AddDataExlAsync(DataInfo);
                        await _pB_MaterialBus.AddDataExlAsync(MaterialList, _redisOptions, _mongodbOptions);
                        await _pB_DesignListBus.AddDataAsync(Data.FirstOrDefault());
                        //int j = 1000;
                        //for (int i = 0; i < DataInfo.Count; i += 1000)
                        //{
                        //    var cList = new List<PB_DesignDetail>();
                        //    cList = DataInfo.Take(j).Skip(i).ToList();
                        //    j += 1000;
                        //    await _pB_DesignDetailBus.AddDataExlAsync(DataInfo);
                        //}
                        // await _pB_DesignListBus.GetOverFlowAdd(DesigId);
                       
                        if (StandardParts.Count > 0)
                        {
                            string str = string.Join(",", StandardParts);
                            ReturnValue = $"数据导入成功,共导入{DataInfo.Count}条数据,本次包含非标件。";
                        }
                        else {
                            ReturnValue = $"数据导入成功,共导入{DataInfo.Count}条数据。";
                        }
                        
                    }
                }
                else if (!flag)
                {
                    return Error(ReturnValue = "数据存在问题！" + ReturnValue);
                }
            }
            catch (Exception ex)
            {
                
                return Error($"文件导入失败请检查文件内容！{cuo}");
            }

            return Success(ReturnValue);

        }

        /// <summary>
        /// 料台导入
        /// </summary>
        /// <param name="file"></param>
        /// <param name="ProjectId">项目主键</param>
        /// <param name="BuildingNumberID">楼号主键</param>
        /// <returns></returns>
        public async Task<AjaxResult> ImportMaterialTable(IFormFile file, string ProjectId, string BuildingNumberID)// file
        {
            List<string> StandardParts = new List<string>();
            string ReturnValue = string.Empty;
            //定义一个bool类型的变量用来做验证
            bool flag = true;
            try
            {
                #region 检查文件
                string fileExt = Path.GetExtension(file.FileName).ToLower();
                //定义一个集合一会儿将数据存储进来,全部一次丢到数据库中保存
                List<PB_DesignList> Data = new List<PB_DesignList>();
                List<PB_DesignDetail> DataInfo = new List<PB_DesignDetail>();
                List<DesignImportDTO> Importlist = new List<DesignImportDTO>();
                List<PB_Material> MaterialList = new List<PB_Material>();
                List<Base_UnitItems> dicMaterialUnit = new List<Base_UnitItems>();
                List<string> liststring = new List<string>();
                MemoryStream ms = new MemoryStream();
                file.CopyTo(ms);
                ms.Seek(0, SeekOrigin.Begin);
                IWorkbook book;
                if (fileExt == ".xlsx")
                {
                    book = new XSSFWorkbook(ms);
                }
                else if (fileExt == ".xls")
                {
                    book = new HSSFWorkbook(ms);
                }
                else
                {
                    book = null;
                }
                ISheet sheet = book.GetSheetAt(0);

                int CountRow = sheet.LastRowNum + 1;//获取总行数
                if (CountRow - 1 == 0)
                {
                    return Error("Excel列表数据项为空!");

                }
                #endregion
                #region 循环验证
                flag = true;
                #endregion
                List<string> listMaterialUnit = new List<string>();
                List<string> ListMaterCount = new List<string>();
                if (flag)
                {
                    int index = 1;
                    PB_DesignList commodity = new PB_DesignList();
                    string KeyID = IdHelper.GetId();
                    for (int i = 0; i < CountRow; i++)
                    {
                        var row = sheet.GetRow(i);
                        //获取主表信息
                        if (i < 5)
                        {

                            if (i == 0)
                            {
                                //绑定项目名称
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    string ExcelKey = row.GetCell(0).ToString();
                                    if (ExcelKey != "QspZCjj7iEGrPWQUg4eqyQ")
                                    {
                                        return Error("导入为非法模板,请下载系统标准模板整理数据导入!");
                                    }
                                }
                            }
                            if (i == 1)
                            {
                                //绑定项目名称
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    commodity.Name = row.GetCell(0).ToString();
                                }
                            }
                            if (i == 2)
                            {
                                //绑定工程名称/ID/创建者ID
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    commodity.Id = KeyID;
                                    commodity.CreatorId = _operator?.UserId;
                                    commodity.ProjectName = row.GetCell(0).ToString().Split("：")?[1];
                                }
                                //绑定机位数量
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    commodity.StandsNums = row.GetCell(6).ToString().Split("：")?[1];
                                }
                            }
                            if (i == 3)
                            {

                                //绑定工程地址
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    commodity.ProjectAddress = row.GetCell(0).ToString().Split("：")?[1];
                                }
                                //绑定架体颜色
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    commodity.Color = row.GetCell(6).ToString().Split("：")[1];
                                }
                            }
                            if (i == 4)
                            {
                                //绑定业务负责人
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    commodity.Salesman = row.GetCell(0).ToString().Split("：")[1];
                                }
                                //绑定业务负责人电话号码
                                if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                                {
                                    commodity.Phone0 = row.GetCell(2).ToString().Split("：")[1];
                                }
                                //绑定进场时间
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    string tmpDate = row.GetCell(6).ToString().Split('：')[1];
                                    if (!tmpDate.IsNullOrEmpty())
                                    {
                                        if (tmpDate.IndexOf("/") > -1)
                                        {
                                            tmpDate = tmpDate.Replace("/", "-");
                                        }
                                        commodity.InDate = Convert.ToDateTime(tmpDate);
                                    }
                                    else
                                    {
                                        commodity.InDate = DateTime.Now;
                                    }
                                }
                            }
                            if (i == 5)
                            {
                                //绑定接货负责人
                                if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                                {
                                    commodity.Receiver = row.GetCell(0).ToString().Split("：")[1];
                                }
                                //绑定接货负责人联系电话
                                if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                                {
                                    commodity.Phone1 = row.GetCell(2).ToString().Split("：")[1];
                                }
                                //绑定下单时间
                                if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                {
                                    var dateInfo = row.GetCell(6).ToString().Split('：')[1];
                                    if (!dateInfo.IsNullOrEmpty())
                                    {
                                        if (dateInfo.IndexOf("/") > -1)
                                        {
                                            dateInfo = dateInfo.Replace("/", "-");
                                        }
                                        commodity.OrderDate = Convert.ToDateTime(dateInfo);
                                    }
                                    else
                                    {
                                        commodity.OrderDate = DateTime.Now;
                                    }
                                }
                            }
                        }
                        else if (i == 6)
                        {
                            //补全其他主表信息
                            commodity.ProjectId = ProjectId;
                            commodity.BuildingNumberID = BuildingNumberID;
                            commodity.Deleted = false;
                            //commodity.InDate = DateTime.Now;
                            //commodity.OrderDate = DateTime.Now;
                            commodity.ExamineStatus = 1;
                            commodity.ApproverStatus = 0;
                            commodity.CreateTime = DateTime.Now;
                            commodity.Approver = commodity.Approver == null ? "" : commodity.Approver;
                            commodity.Calculation = commodity.Calculation == null ? "" : commodity.Calculation;
                            commodity.Code = commodity.Code == null ? "" : commodity.Code;
                            commodity.ContractNo = commodity.ContractNo == null ? "" : commodity.ContractNo;
                            commodity.DeptId = _operator?.Property.DepartmentId;
                            commodity.Examiner = commodity.Examiner == null ? "" : commodity.Examiner;
                            commodity.Name = commodity.Name == null ? "" : commodity.Name;
                            commodity.PicUrl = commodity.PicUrl == null ? "" : commodity.PicUrl;
                            commodity.ProjectId = commodity.ProjectId == null ? "" : commodity.ProjectId;
                            commodity.QrCode = "12," + KeyID;
                            commodity.Remarks = commodity.Remarks == null ? "" : commodity.Remarks;
                            commodity.IsPrint = false;
                            commodity.Type = 2;
                            Data.Add(commodity);
                        }
                        else if (i >= 7)
                        {
                            //绑定明细表
                            //PB_DesignDetail pB_DesignDetail = new PB_DesignDetail();
                            DesignImportDTO desdata = new DesignImportDTO();
                            //如果第一列不为空（判断）
                            if (!row.GetCell(0).IsNullOrEmpty())
                            {

                                //如果成品编号不存在则跳过
                                if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                                {
                                    //获取当前行号
                                    desdata.Slot = IdHelper.GetId();
                                    if (row.GetCell(1) != null && row.GetCell(1).ToString().Trim().Length > 0)
                                    {
                                        desdata.Name = row.GetCell(1).ToString();
                                    }
                                    if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                                    {
                                        desdata.ProductCode = row.GetCell(2).ToString();
                                    }
                                    //绑定理论重量
                                    if (row.GetCell(3) != null && row.GetCell(3).ToString().Trim().Length > 0)
                                    {
                                        if (row.GetCell(3).CellType == CellType.Formula)
                                        {
                                            row.GetCell(3).SetCellType(CellType.String);
                                            desdata.Weight = Convert.ToDouble(row.GetCell(3).StringCellValue);
                                        }
                                        else
                                        {
                                            //desdata.Weight = PB_DesignListController.IsNumber(row.GetCell(3).ToString()) == true ? Convert.ToDouble(row.GetCell(3).ToString()) : 0;
                                            desdata.Weight = Convert.ToDouble(row.GetCell(3).ToString());
                                        }

                                    }
                                    if (row.GetCell(4) != null && row.GetCell(4).ToString().Trim().Length > 0)
                                    {
                                        desdata.unitName = row.GetCell(4).ToString();
                                    }
                                    if (row.GetCell(5) != null && row.GetCell(5).ToString().Trim().Length > 0)
                                    {
                                        desdata.Standard = row.GetCell(5).StringCellValue.ToString();
                                        if (row.GetCell(5).StringCellValue.ToString() == "是")
                                        {
                                            listMaterialUnit.Add(row.GetCell(4).ToString());
                                        }
                                    }

                                    //绑定设计数量
                                    if (row.GetCell(6) != null && row.GetCell(6).ToString().Trim().Length > 0)
                                    {
                                        //desdata.Nums = row.GetCell(6).ToString();
                                        if (row.GetCell(6).CellType == CellType.Formula)
                                        {
                                            row.GetCell(6).SetCellType(CellType.String);
                                            desdata.Nums = Convert.ToDouble(row.GetCell(6).StringCellValue);
                                        }
                                        else
                                        {
                                            desdata.Nums = PB_DesignListController.IsNumber(row.GetCell(6).ToString()) == true ? Convert.ToDouble(row.GetCell(6).ToString()) : 0;
                                        }
                                    }
                                    //绑定实发数量
                                    if (row.GetCell(7) != null && row.GetCell(7).ToString().Trim().Length > 0)
                                    {
                                        if (row.GetCell(7).CellType == CellType.Formula)
                                        {
                                            row.GetCell(7).SetCellType(CellType.String);
                                            desdata.RealNums = Convert.ToDouble(row.GetCell(7).StringCellValue);
                                        }
                                        else
                                        {
                                            if (row.GetCell(7).ToString().Trim() == "相同")
                                            {
                                                desdata.RealNums = desdata.Nums;
                                            }
                                            else
                                            {
                                                if (row.GetCell(7).CellType == CellType.Formula)
                                                {
                                                    row.GetCell(7).SetCellType(CellType.String);
                                                    desdata.RealNums = Convert.ToDouble(row.GetCell(7).StringCellValue);
                                                }
                                                else
                                                {
                                                    desdata.RealNums = PB_DesignListController.IsNumber(row.GetCell(7).ToString()) == true ? Convert.ToDouble(row.GetCell(7).ToString()) : 0;
                                                }
                                            }
                                        }
                                    }
                                    //绑定备注
                                    if (row.GetCell(8) != null && row.GetCell(8).ToString().Trim().Length > 0)
                                    {
                                        desdata.Remarks = row.GetCell(8).ToString();
                                        //pB_DesignDetail.Remarks += row.GetCell(8).ToString();
                                    }
                                    Importlist.Add(desdata);
                                    //DataInfo.Add(pB_DesignDetail);
                                    index++;
                                }
                            }
                        }
                    }
                    //去重成品编码并赋给新集合（listMaterialTypSpace）
                    //var listMaterialTypSpace = DataInfo.Select(s => s.MaterialId).Select(s => s.Trim()).Distinct().ToList();
                    //var dicMaterialSpace = _pB_MaterialBus.GetQueryable<PB_Material>().Where(w => listMaterialTypSpace.Contains(w.ProductCode)).ToDictionary(k => k.Id, v => v.ProductCode);


                    var listMaterialTypSpace = Importlist.Select(s => s.ProductCode).ToList();
                    //获取物料主键ID                    
                    var dicMaterialSpace = _pB_MaterialBus.GetQueryableByProductCode<PB_ProductCodeIdsDTO>(listMaterialTypSpace);
                    var dicMaterialState = _pB_MaterialBus.GetQueryableByProductUsageStatusCode<PB_ProductCodeIdsDTO>(listMaterialTypSpace);
                    var materialTypedata = _pB_MaterialBus.GetDesignListMaterialTypeAsync();
                    string matercode = materialTypedata.MaterialCode;

                    if (listMaterialUnit.Count > 0)
                    {
                        dicMaterialUnit = _pB_MaterialBus.GetUnitItems<Base_UnitItems>(listMaterialUnit);
                    }


                    var listdata = _pB_MaterialBus.GetQueryable<PB_DesignList>().Where(w => w.ProjectId == ProjectId && w.BuildingNumberID == BuildingNumberID && w.Type == 1).FirstOrDefault();
                    if (listdata != null)
                    {
                        liststring.Add(listdata.ProjectName);
                    }

                    string SpaceString = "";
                    string StateString = "";
                    //string NumberString = "";
                    //插入子表
                    foreach (var item in Importlist)
                    {
                        PB_DesignDetail DesignDetail = new PB_DesignDetail();
                        DesignDetail.Id = IdHelper.GetId();
                        DesignDetail.ParentId = KeyID;
                        DesignDetail.Nums = Convert.ToDouble(item.Nums);
                        DesignDetail.RealNums = Convert.ToDouble(item.RealNums);
                        DesignDetail.ShipmentNums = 0;
                        DesignDetail.CreatorId = _operator?.UserId;
                        DesignDetail.CreateTime = DateTime.Now;
                        DesignDetail.State = 0;
                        DesignDetail.VersionNumber = 1;
                        DesignDetail.ShipmentNums = 0;
                        //DesignDetail.Slot = item.Slot;
                        DesignDetail.Slot = IdHelper.GetId();
                        DesignDetail.Remarks = item.Remarks;
                        DesignDetail.Weight = item.Weight;
                        DesignDetail.OneStep = Convert.ToDouble(item.RealNums);
                        DesignDetail.TwoStep = item.TwoStep;
                        DesignDetail.ThreeStep = item.ThreeStep;
                        DesignDetail.FourStep = item.FourStep;
                        DesignDetail.FiveStep = item.FiveStep;
                        DesignDetail.SixStep = item.SixStep;
                        DesignDetail.SevenStep = item.SevenStep;
                        DesignDetail.EightStep = item.EightStep;
                        DesignDetail.NineStep = item.NineStep;
                        DesignDetail.TenStep = item.TenStep;
                        DesignDetail.ElevenStep = item.ElevenStep;
                        DesignDetail.TwelveStep = item.TwelveStep;
                        DesignDetail.ThirteenStep = item.ThirteenStep;
                        DesignDetail.FourteenStep = item.FourteenStep;
                        DesignDetail.FifteenStep = item.FifteenStep;
                        if (item.Standard == "是")
                        {
                            //通过成品编号绑定物料主键ID
                            if (dicMaterialSpace.Any(s => s.ProductCode == item.ProductCode.Trim()))
                            {

                                //插入非标件提示
                                StandardParts.Add(item.ProductCode);

                                //当前成品编码存在
                                DesignDetail.MaterialId = dicMaterialSpace.FirstOrDefault(x => x.ProductCode == item.ProductCode.Trim()).Id;
                                if (ListMaterCount.Contains(DesignDetail.MaterialId))
                                {
                                    var db = _pB_DesignListBus.GetQueryableOK<PB_Material>().Where(m => m.Id == DesignDetail.MaterialId).FirstOrDefault();
                                    return Error(ReturnValue = $"导入数据物料:{db.Name}重复");
                                }
                                ListMaterCount.Add(DesignDetail.MaterialId);
                            }
                            else
                            {
                                PB_Material madata = new PB_Material();
                                string maid = IdHelper.GetId();
                                madata.Id = maid;
                                madata.DeptId = _operator.Property?.FactoryId;
                                madata.Name = item.Name;
                                if (matercode.IsNullOrEmpty())
                                {
                                    madata.Code = "16.000001";
                                    matercode = "000001";
                                }
                                else
                                {
                                    if (matercode.Length == 6)
                                    {
                                        string code = "000000" + (Convert.ToInt32(matercode) + 1);
                                        madata.Code = "16." + code.Substring(code.Length - 6, 6);
                                        matercode = code.Substring(code.Length - 6, 6);
                                    }
                                    else
                                    {
                                        madata.Code = "16." + (Convert.ToInt32(matercode) + 1);
                                        matercode = (Convert.ToInt32(matercode) + 1).ToString();
                                    }
                                }
                                var madataProductCode = await _pB_DesignListBus.GetProductCodeData(item.ProductCode);
                                if (madataProductCode != null)
                                {
                                    return Error(ReturnValue = "成品编码已存在:" + item.ProductCode);
                                }
                                madata.ProductCode = item.ProductCode;

                                madata.Spec = item.ProductCode;
                                madata.SimpleName = item.Name;
                                madata.MaterialTypeId = materialTypedata.Id;
                                if (dicMaterialUnit.Any(s => s.Name == item.unitName.Trim()))
                                {
                                    //当前成品编码存在
                                    madata.UnitGroupId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).GroupId;
                                    madata.storageId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).Id;
                                    madata.saleId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).Id;
                                    madata.purchaseId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).Id;
                                    madata.productionId = dicMaterialUnit.FirstOrDefault(x => x.Name == item.unitName.Trim()).Id;
                                }
                                madata.Weight = Convert.ToDouble(item.Weight);
                                madata.IsProduct = true;
                                madata.IsMine = true;
                                madata.IsBuy = false;
                                madata.HasRef = false;
                                madata.IsVirtualkey = false;
                                madata.IsLabour = false;
                                madata.IsNumber = true;
                                madata.IsKeyprice = false;
                                madata.IsCruxMaterial = false;
                                madata.IsQuantity = false;
                                madata.IsExtensiveProduction = false;
                                madata.IsBecode = true;
                                madata.PackageNum = 1;
                                madata.IsSingle = false;
                                madata.IsStandard = false;
                                madata.IsSpec = false;
                                madata.CreateTime = DateTime.Now;
                                madata.CreatorId = _operator.UserId;
                                madata.Deleted = false;
                                madata.UsageStatus = true;
                                MaterialList.Add(madata);
                                DesignDetail.MaterialId = maid;

                                if (ListMaterCount.Contains(maid))
                                {
                                    var db = _pB_DesignListBus.GetQueryableOK<PB_Material>().Where(m => m.Id == maid).FirstOrDefault();
                                    return Error(ReturnValue = $"导入数据物料:{db.Name}重复");
                                }
                                ListMaterCount.Add(maid);
                            }
                        }
                        else
                        {
                            //通过成品编号绑定物料主键ID
                            if (dicMaterialSpace.Any(s => s.ProductCode == item.ProductCode.Trim()))
                            {
                                //当前成品编码存在
                                DesignDetail.MaterialId = dicMaterialSpace.FirstOrDefault(x => x.ProductCode == item.ProductCode.Trim()).Id;

                                if (ListMaterCount.Contains(DesignDetail.MaterialId))
                                {
                                    var db = _pB_DesignListBus.GetQueryableOK<PB_Material>().Where(m => m.Id == DesignDetail.MaterialId).FirstOrDefault();
                                    return Error(ReturnValue = $"导入数据物料:{db.Name}重复");
                                }
                                ListMaterCount.Add(DesignDetail.MaterialId);
                            }
                            else
                            {
                                //当前成品编码不存在
                                SpaceString += "[编号：" + item.ProductCode.Trim() + "],";
                            }

                            if (dicMaterialState.Any(s => s.ProductCode == item.ProductCode.Trim()))
                            {
                                //当前成品编码存在
                                DesignDetail.MaterialId = dicMaterialState.FirstOrDefault(x => x.ProductCode == item.ProductCode.Trim()).Id;

                            }
                            else
                            {
                                //当前成品编码不存在
                                StateString += "[编号：" + item.ProductCode.Trim() + "],";
                            }

                        }
                        DataInfo.Add(DesignDetail);
                    }

                    //成品编码不存在则return
                    if (!string.IsNullOrEmpty(SpaceString))
                    {
                        //string aaa = "物料编码不存在！" + SpaceString.Substring(0, SpaceString.Length - 1);
                        return Error(ReturnValue = "成品编码不存在！" + SpaceString.Substring(0, SpaceString.Length - 1));
                    }
                    if (!string.IsNullOrEmpty(StateString))
                    {

                        return Error(ReturnValue = "物料已停用" + StateString.Substring(0, SpaceString.Length - 1));
                    }
                    //主表存在则返回
                    if (liststring.Count() > 0)
                    {
                        return Error(ReturnValue = "导入的数据中有已存在数据库中,请不要重复导入");
                    }
                    if (DataInfo.Count > 0)
                    {
                        //插入主子表
                        await _pB_DesignDetailBus.AddDataExlAsync(DataInfo);
                        await _pB_MaterialBus.AddDataExlAsync(MaterialList, _redisOptions, _mongodbOptions);
                        await _pB_DesignListBus.AddDataAsync(Data.FirstOrDefault());
                        //int j = 1000;
                        //for (int i = 0; i < DataInfo.Count; i += 1000)
                        //{
                        //    var cList = new List<PB_DesignDetail>();
                        //    cList = DataInfo.Take(j).Skip(i).ToList();
                        //    j += 1000;
                        //    await _pB_DesignDetailBus.AddDataExlAsync(DataInfo);
                        //}
                        ReturnValue = $"数据导入成功,共导入{DataInfo.Count}条数据。";
                    }
                }
                else if (!flag)
                {
                    return Error("文件导入失败请检查文件内容");
                }
            }
            catch (Exception ex)
            {
                return Error("文件导入失败请检查文件内容");
            }

            return Success(ReturnValue);

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="Projectid">项目id</param>
        /// <param name="bulid">楼号id</param>
        /// <returns></returns>
        [HttpPost]
        public async Task<List<ListSelectionDto>> GetListSelection(SSS data)
        {
            return await _pB_DesignListBus.GetListSelection(data);
        }
        /// <summary>
        /// 判断字符串是否是数字
        /// </summary>
        public static bool IsNumber(string s)
        {
            if (string.IsNullOrWhiteSpace(s)) return false;
            const string pattern = "^[0-9]*$";
            Regex rx = new Regex(pattern);
            return rx.IsMatch(s);
        }
        /// <summary>
        /// 查询项目楼号是否存在
        /// </summary>
        /// <param name="ProjectId">项目Id</param>
        /// <param name="Buid">楼号ID</param>
        /// <param name="Type">设计清单类型</param>
        /// <returns></returns>
        [HttpPost]
        public async Task<AjaxResult> GetallCount(string ProjectId, string Buid, int Type)
        {
            return await _pB_DesignListBus.GetallCount(ProjectId, Buid, Type);
        }

        /// <summary>
        /// 设计清单打印
        /// </summary>
        /// <param name="ParentId">清单编号</param>
        /// <returns></returns>
        [HttpPost]
        public async Task<PB_DesignListPrint> GetDataListPrint(string ParentId)
        {
            return await _pB_DesignListBus.GetDataListPrint(ParentId);

        }

        /// <summary>
        /// 获取设计清单类型
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public async Task<List<PB_DesignListType>> GetDesignListTypeList()
        {
            return await _pB_DesignListBus.GetDesignListTypeListAsync();
        }
        /// <summary>
        /// 设计清单导出
        /// </summary>
        /// <param name="id"></param>
        /// <param name="deatiltype"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> GetDesignListExport(string id, int deatiltype)
        {
            //查询数据
            DesignListDataDTO listFaultInfo = await _pB_DesignListBus.GetDesignListDataAsync(id, deatiltype);
            //二：代码创建一个Excel表格（这里称为工作簿）
            //创建Excel文件的对象 工作簿(调用NPOI文件)
            HSSFWorkbook excelBook = new HSSFWorkbook();
            ICellStyle style1 = excelBook.CreateCellStyle();//声明style1对象，设置Excel表格的样式
            ICellStyle style2 = excelBook.CreateCellStyle();
            ICellStyle styleRight = excelBook.CreateCellStyle();
            ICellStyle style3 = excelBook.CreateCellStyle();
            ICellStyle style4 = excelBook.CreateCellStyle();
            IFont font = excelBook.CreateFont();
            font.Color = IndexedColors.Black.Index;
            style3.SetFont(font);
            style1.Alignment = HorizontalAlignment.Justify;//两端自动对齐（自动换行）
            style1.VerticalAlignment = VerticalAlignment.Center;
            style2.Alignment = HorizontalAlignment.Center;
            styleRight.Alignment = HorizontalAlignment.Right;
            style2.VerticalAlignment = VerticalAlignment.Center;
            style3.Alignment = HorizontalAlignment.Center;
            style3.VerticalAlignment = VerticalAlignment.Center;

            IDataFormat dataformat = excelBook.CreateDataFormat();
            ICellStyle cStyle1 = excelBook.CreateCellStyle();

            cStyle1.DataFormat = dataformat.GetFormat("0.00_ ");//单元格格式 数值

            //创建Excel工作表 Sheet=故障码信息
            //标题
            ISheet sheet1 = excelBook.CreateSheet("设计清单信息");
            //单元格合并 
            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 7));//合并单元格,4个参数依次为为startRow,endRow,startCol,endCo
            sheet1.AddMergedRegion(new CellRangeAddress(1, 1, 0, 4));//合并单元格,4个参数依次为为startRow,endRow,startCol,endCo
            sheet1.AddMergedRegion(new CellRangeAddress(1, 1, 5, 7));//合并单元格,4个参数依次为为startRow,endRow,startCol,endCo
            sheet1.AddMergedRegion(new CellRangeAddress(2, 2, 0, 4));//合并单元格,4个参数依次为为startRow,endRow,startCol,endCo
            sheet1.AddMergedRegion(new CellRangeAddress(2, 2, 5, 7));//合并单元格,4个参数依次为为startRow,endRow,startCol,endCo
            sheet1.AddMergedRegion(new CellRangeAddress(3, 3, 0, 1));//合并单元格,4个参数依次为为startRow,endRow,startCol,endCo
            sheet1.AddMergedRegion(new CellRangeAddress(3, 3, 2, 4));//合并单元格,4个参数依次为为startRow,endRow,startCol,endCo
            sheet1.AddMergedRegion(new CellRangeAddress(3, 3, 5, 7));//合并单元格,4个参数依次为为startRow,endRow,startCol,endCo
            sheet1.AddMergedRegion(new CellRangeAddress(4, 4, 0, 1));//合并单元格,4个参数依次为为startRow,endRow,startCol,endCo
            sheet1.AddMergedRegion(new CellRangeAddress(4, 4, 2, 4));//合并单元格,4个参数依次为为startRow,endRow,startCol,endCo
            sheet1.AddMergedRegion(new CellRangeAddress(4, 4, 5, 7));//合并单元格,4个参数依次为为startRow,endRow,startCol,endCo
            sheet1.AddMergedRegion(new CellRangeAddress(5, 5, 0, 1));//合并单元格,4个参数依次为为startRow,endRow,startCol,endCo
            sheet1.AddMergedRegion(new CellRangeAddress(5, 5, 2, 4));//合并单元格,4个参数依次为为startRow,endRow,startCol,endCo
            sheet1.AddMergedRegion(new CellRangeAddress(5, 5, 5, 7));//合并单元格,4个参数依次为为startRow,endRow,startCol,endCo
            //设计清单名称
            IRow row0Temp = sheet1.CreateRow(0);
            row0Temp.CreateCell(0).SetCellValue(listFaultInfo.Data.Name);
            row0Temp.GetCell(0).CellStyle = style2;//初始化样式
            row0Temp.Height = 400;

            IRow row1Temp = sheet1.CreateRow(1);
            row1Temp.CreateCell(0).SetCellValue("工程名称:" + listFaultInfo.Data.ProjectName);
            row1Temp.CreateCell(5).SetCellValue("机位数量:" + listFaultInfo.Data.StandsNums);
            row1Temp.Height = 400;



            IRow row2Temp = sheet1.CreateRow(2);
            row2Temp.CreateCell(0).SetCellValue("工程地址:" + listFaultInfo.Data.ProjectAddress);
            row2Temp.CreateCell(5).SetCellValue("提升方式:" + listFaultInfo.Data.LiftingMode);
            row2Temp.Height = 400;



            IRow row3Temp = sheet1.CreateRow(3);
            row3Temp.CreateCell(0).SetCellValue("业务负责人:" + listFaultInfo.Data.Salesman);
            row3Temp.CreateCell(2).SetCellValue("联系电话:" + listFaultInfo.Data.Phone0);
            row3Temp.CreateCell(5).SetCellValue("架体步高:" + listFaultInfo.Data.StepHeight);
            row3Temp.Height = 400;

            IRow row4Temp = sheet1.CreateRow(4);
            row4Temp.CreateCell(0).SetCellValue("接货负责人:" + listFaultInfo.Data.Receiver);
            row4Temp.CreateCell(2).SetCellValue("联系电话:" + listFaultInfo.Data.Phone1);
            row4Temp.CreateCell(5).SetCellValue("架体颜色:" + listFaultInfo.Data.Color);
            row4Temp.Height = 400;

            IRow row5Temp = sheet1.CreateRow(5);
            row5Temp.CreateCell(0).SetCellValue("进场时间:" + listFaultInfo.Data.InDate);
            row5Temp.CreateCell(2).SetCellValue("下单日期:" + listFaultInfo.Data.OrderDate);
            row5Temp.CreateCell(5).SetCellValue("网版样式:" + listFaultInfo.Data.Style);
            row5Temp.Height = 400;
            ICellStyle style6 = excelBook.CreateCellStyle();

            style6.DataFormat = 194;
            //给Sheet(清单信息）添加第一行的头部标题
            IRow row1 = sheet1.CreateRow(6);
            //给标题的每一个单元格赋值
            row1.CreateCell(0).SetCellValue("物料名称");//0
            row1.CreateCell(1).SetCellValue("编号");//1
            row1.CreateCell(2).SetCellValue("理论重量");//2
            row1.CreateCell(3).SetCellValue("单位");//3
            row1.CreateCell(4).SetCellValue("是否非标件");//4
            row1.CreateCell(5).SetCellValue("设计数量");//5
            row1.CreateCell(6).SetCellValue("实发数量");//6
            row1.CreateCell(7).SetCellValue("备注");//7
            row1.CreateCell(8).SetCellValue("第一步");//8
            row1.CreateCell(9).SetCellValue("第二步");//9
            row1.CreateCell(10).SetCellValue("第三步");//10
            row1.CreateCell(11).SetCellValue("第四步");//11
            row1.CreateCell(12).SetCellValue("第五步");//12
            row1.CreateCell(13).SetCellValue("第六步");//13
            row1.CreateCell(14).SetCellValue("第七步");//14
            row1.CreateCell(15).SetCellValue("第八步");//15
            row1.CreateCell(16).SetCellValue("第九步");//16
            row1.CreateCell(17).SetCellValue("第十步");//17
            row1.CreateCell(18).SetCellValue("第十一步");//18
            row1.CreateCell(19).SetCellValue("第十二步");//19
            row1.CreateCell(20).SetCellValue("第十三步");//20
            row1.CreateCell(21).SetCellValue("第十四步");//21
            row1.CreateCell(22).SetCellValue("第十五步");//22
       
            //添加数据行：将表格数据逐步写入sheet1各个行中（也就是给每一个单元格赋值）

            for (int i = 0; i < listFaultInfo.Sub.Count(); i++)
            {

                //sheet1.CreateRow(i).
                //创建行
                IRow rowTemp = sheet1.CreateRow(i + 7);
          

                //物料名称
                rowTemp.CreateCell(0).SetCellValue(listFaultInfo.Sub[i].Name);
                //物料编号
                rowTemp.CreateCell(1).SetCellValue(listFaultInfo.Sub[i].Code);



                //理论重量
                rowTemp.CreateCell(2).SetCellValue(listFaultInfo.Sub[i].Weight.ToString()==""?0: Convert.ToDouble(listFaultInfo.Sub[i].Weight.ToString()));
                rowTemp.GetCell(2).CellStyle = style6;
                //单位
                rowTemp.CreateCell(3).SetCellValue(listFaultInfo.Sub[i].UnitGroupName);
                //是否非标件
                rowTemp.CreateCell(4).SetCellValue(listFaultInfo.Sub[i].IsStandard == false ? "否" : "是");
                //设计数量
                rowTemp.CreateCell(5).SetCellValue(listFaultInfo.Sub[i].Nums.ToString()==""?0: Convert.ToDouble(listFaultInfo.Sub[i].Nums.ToString()));
                rowTemp.GetCell(5).CellStyle = style6;
                //实发数量
                rowTemp.CreateCell(6).SetCellValue(listFaultInfo.Sub[i].RealNums.ToString()==""?0: Convert.ToDouble(listFaultInfo.Sub[i].RealNums.ToString()));
                rowTemp.GetCell(6).CellStyle = style6;
                //备注
                rowTemp.CreateCell(7).SetCellValue(listFaultInfo.Sub[i].Remarks);
                //第一步
                rowTemp.CreateCell(8).SetCellValue(listFaultInfo.Sub[i].OneStep.ToString()==""?0: Convert.ToDouble(listFaultInfo.Sub[i].OneStep.ToString()));
                //第二步
                rowTemp.CreateCell(9).SetCellValue(listFaultInfo.Sub[i].TwoStep.ToString()==""?0: Convert.ToDouble(listFaultInfo.Sub[i].TwoStep.ToString()));
                //第三步
                string aa = listFaultInfo.Sub[i].ThreeStep.ToString();
                rowTemp.CreateCell(10).SetCellValue(listFaultInfo.Sub[i].ThreeStep.ToString()==""?0: Convert.ToDouble(listFaultInfo.Sub[i].ThreeStep.ToString()));
                //第四步
                rowTemp.CreateCell(11).SetCellValue(listFaultInfo.Sub[i].FourStep.ToString()==""?0: Convert.ToDouble(listFaultInfo.Sub[i].FourStep.ToString()));
                //第五步
                rowTemp.CreateCell(12).SetCellValue(listFaultInfo.Sub[i].FiveStep.ToString() == ""?0: Convert.ToDouble(listFaultInfo.Sub[i].FiveStep.ToString())) ;
                //第六步
                rowTemp.CreateCell(13).SetCellValue(listFaultInfo.Sub[i].SixStep.ToString()==""?0: Convert.ToDouble(listFaultInfo.Sub[i].SixStep.ToString()));
                //第七步
                rowTemp.CreateCell(14).SetCellValue(listFaultInfo.Sub[i].SevenStep.ToString()==""?0: Convert.ToDouble(listFaultInfo.Sub[i].SevenStep.ToString()));
                //第八步
                rowTemp.CreateCell(15).SetCellValue(listFaultInfo.Sub[i].EightStep.ToString()==""?0: Convert.ToDouble(listFaultInfo.Sub[i].EightStep.ToString()));
                //第九步
                rowTemp.CreateCell(16).SetCellValue(listFaultInfo.Sub[i].NineStep.ToString()==""?0: Convert.ToDouble(listFaultInfo.Sub[i].NineStep.ToString()));
                //第十步
                rowTemp.CreateCell(17).SetCellValue(listFaultInfo.Sub[i].TenStep.ToString()==""?0: Convert.ToDouble(listFaultInfo.Sub[i].TenStep.ToString()));
                //第十-步
                rowTemp.CreateCell(18).SetCellValue(listFaultInfo.Sub[i].ElevenStep.ToString()==""?0: Convert.ToDouble(listFaultInfo.Sub[i].ElevenStep.ToString()));
                //第十二步
                rowTemp.CreateCell(19).SetCellValue(listFaultInfo.Sub[i].TwelveStep.ToString()==""?0: Convert.ToDouble(listFaultInfo.Sub[i].TwelveStep.ToString()));
                //第十三步
                rowTemp.CreateCell(20).SetCellValue(listFaultInfo.Sub[i].ThirteenStep.ToString()==""?0: Convert.ToDouble(listFaultInfo.Sub[i].ThirteenStep.ToString()));
                //第十四步
                rowTemp.CreateCell(21).SetCellValue(listFaultInfo.Sub[i].FourteenStep.ToString()==""?0: Convert.ToDouble(listFaultInfo.Sub[i].FourteenStep.ToString()));
                //第十五步
                rowTemp.CreateCell(22).SetCellValue(listFaultInfo.Sub[i].FifteenStep.ToString()==""?0: Convert.ToDouble(listFaultInfo.Sub[i].FifteenStep.ToString()));
            }
            //输出的文件名称
            string fileName = "清单导出" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ffff") + ".xls";
            //把Excel转为流，输出
            //创建文件流
            System.IO.MemoryStream bookStream = new System.IO.MemoryStream();
            //将工作薄写入文件流
            excelBook.Write(bookStream);

            //输出之前调用Seek（偏移量，游标位置) 把0位置指定为开始位置
            bookStream.Seek(0, System.IO.SeekOrigin.Begin);
            //Stream对象,文件类型,文件名称
            return File(bookStream, "application/vnd.ms-excel", fileName);
        }
        /// <summary>
        /// 补给单调用
        /// </summary>
        /// <param name="DesigId"></param>
        /// <returns></returns>
        /// 
        [HttpPost]
        public async Task GetOverFlowMessage(string DesigId)
        {
            await _pB_DesignListBus.GetOverFlowMessage(DesigId);
        }
        [HttpPost]
        public async Task<string> GetFileAddress(string DesigId, string Type)
        {
            return await _pB_DesignListBus.GetFileAddress(DesigId, Type);
        }
        [HttpPost]
        public async Task MaintenanceSteps(PB_designDetaliList data, string Parentid) {
            await _pB_DesignListBus.MaintenanceSteps(data, Parentid);
        }
        [HttpPost]
         public async Task<AjaxResult> GetDesignPiakMain(string Id) {
          return  await _pB_DesignListBus.GetDesignPiakMain(Id);
        }
        [HttpPost]
        public async Task DesignListDelete(DesignListDelete data) {
            await _pB_DesignListBus. DesignListDelete(data);
        }
        #endregion
    }
}